Changing the font color of a cell based on value in another cell

[kawi6rr]

I can get this to work on a single range of cells but I can’t get it to work on a second or third range of cells. My goal is to be able to change the “Goal” value in a primary cell and have the range of cells associated with that goal change colors from red, green, or black if they meet, exceed, or are below the goal.

Goal 95% then any cell that is less than 95% the font color will be red.
Goal 95% then any cell that is Greater than 95% the font color will be red.

and so on...

Here's the code I have at the moment and am currently getting a compile error of Next without For and I'm pretty much stuck at this point.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim Stroke As Range
    Dim IMM As Range
    Dim Measure As String
    
    Set rng = Range("F4:K8")
    Set Stroke = Range("F4:K4")
    Set IMM = Range("F5:K5")
    'Measure = Range("D4")
    
    For Each cell In rng
    'Stroke
        If Stroke Then
            Measure = Range("D4")
        If cell = Measure Then
            cell.Font.Color = 0     'Black
        ElseIf cell > Measure Then
            cell.Font.Color = 32768 'Green
        ElseIf cell < Measure Then
            cell.Font.Color = 128   'Red
        End If
    'Immunization
        If IMM Then
            Measure = Range("D5")
        If cell = Measure Then
            cell.Font.Color = 0     'Black
        ElseIf cell > Measure Then
            cell.Font.Color = 32768 'Green
        ElseIf cell < Measure Then
            cell.Font.Color = 128   'Red
        End If
    Next cell
    
End Sub

Any help is appreciated, thanks.

[kawi6rr]

I fixed the Loop error but it's still not working as intended.

[protonLeah]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim Stroke As Range
    Dim IMM As Range
    Dim Measure As String
    
    Set rng = Range("F4:K8")
    Set Stroke = Range("F4:K4")
    Set IMM = Range("F5:K5")
    'Measure = Range("D4")
    
    For Each cell In rng
    'Stroke
        If Stroke Then
            Measure = Range("D4")
            If cell = Measure Then
                cell.Font.Color = 0     'Black
            ElseIf cell > Measure Then
                cell.Font.Color = 32768 'Green
            ElseIf cell < Measure Then
                cell.Font.Color = 128   'Red
            End If  'cell = measure
        End If 'missing stroke test
    'Immunization
        If IMM Then
            Measure = Range("D5")
            If cell = Measure Then
                cell.Font.Color = 0     'Black
            ElseIf cell > Measure Then
                cell.Font.Color = 32768 'Green
            ElseIf cell < Measure Then
                cell.Font.Color = 128   'Red
            End If  ' end if cell = measure
        End If 'missing for IMM test
    Next cell
End Sub

[kawi6rr]

Thanks you that cleared up the error but it's still not changing the cell font colors.

[LJMetzger]

I don't understand the following line:

If Stroke Then

Stroke is defined as a range of addresses, which does not have a value, I get a runtime error on that line. Elements of the range have values, but the range itself does not have a value.

Please let us know what you are trying to do with that line.

Lewis

[kawi6rr]

I thought that could be in issue.

I dimmed Stroke as range then set the range
Set Stroke = Range("F4:K4")

I'm calling that stroke range to set the measure value for that range. I don’t actually know if that’s the proper/correct way to call it.

[kawi6rr]

SOLVED!

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim StrokeRange As Range
    Dim IMMRange As Range
    Dim cell As Range
    Dim Measure As String
    
    Set StrokeRange = Range("F4:K4")
    Set IMMRange = Range("F5:K5")
    'Measure = Range("D4")
    
    For Each cell In StrokeRange
      Measure = Range("D4")
        If cell = Measure Then
            cell.Font.Color = 0     'Black
        ElseIf cell > Measure Then
            cell.Font.Color = 32768 'Green
        ElseIf cell < Measure Then
            cell.Font.Color = 128   'Red
     End If
    Next cell

    For Each cell In IMMRange
      Measure = Range("D5")
        If cell = Measure Then
            cell.Font.Color = 0     'Black
        ElseIf cell > Measure Then
            cell.Font.Color = 32768 'Green
        ElseIf cell < Measure Then
            cell.Font.Color = 128   'Red
     End If
    Next cell
    
End Sub