用宏，满足条件的填色

heiyanzhiying

如图，用   宏    给小于-18的填上红色，大于-16的填充黄色？

魚。。

牛！也不知道这-17应该黄了还是红了

tho1mas2

用条件格式不方便吗

heiyanzhiying

魚。。 发表于 2024-6-13 11:13
牛！也不知道这-17应该黄了还是红了

我的错，表达逻辑错了，不好意思

heiyanzhiying

tho1mas2 发表于 2024-6-13 11:27
用条件格式不方便吗

几百个表都要检索标注，用条件会死人的

魚。。

heiyanzhiying 发表于 2024-6-13 11:30
几百个表都要检索标注，用条件会死人的

嗯，如果只是标注一次的话，确实用代码更好些。

heiyanzhiying

找到一行代码
     Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    '表头
    With ws
    Dim rng As Range
    Set rng = ws.Range("C2:E26")
   
    Dim cell As Range
    For Each cell In rng
        If cell.Value < -24 Then
            cell.Interior.Color = RGB(255, 0, 0)
        End If
        Next cell
现在问题是新打开一个表，运行只有PERSONAL.XLSB被改了。新表没反应，需要改什么？哪位大神知道

13570449347

Sub ColorizeCells()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim rng As Range
    Dim cell As Range
    Dim startRow As Long
    startRow = 2
    Set rng = ws.Range("C" & startRow & ":J" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
    For Each cell In rng
        If cell.Value < -18 Then
            cell.Interior.Color = RGB(255, 0, 0)
        ElseIf cell.Value > -16 Then
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
End Sub

13570449347

Sub ColorizeCells()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim rng As Range
    Dim cell As Range
    Dim startRow As Long
    startRow = 2
    Set rng = ws.Range("C" & startRow & ":J" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
    For Each cell In rng
        If cell.Value < -18 Then
            cell.Interior.Color = RGB(255, 0, 0)
        ElseIf cell.Value > -16 Then
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
End Sub

heiyanzhiying

13570449347 发表于 2024-6-13 15:01
Sub ColorizeCells()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

失败，中断了