Thread: Conditional Formatting of Font Color based on Values in another cell

Haven't looked at your workbook but.....see below for the code to change cell formatting. Put the vba code into the change event of your worksheet so it runs every time a user enters a value anywhere.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("H:H")) Is Nothing Then             ' ensures only changes made to column H have an effect
 
     If target.value > 0 and target.value < 5 then
          cells(target.row, target.column - 2).font.colorindex = 1
     else
          cells(target.row, target.column - 2).font.colorindex = 0
     end if

end if

end sub

I've put it in but I get an error. Also, not all the cells in column H will have values between 1-5, and it depends on the rest of the macro how many rows there will actually be... bu for this one, I need something that says
"If all cells H4:H25 have a value between 1-5, then font colour in all cells F4:F25 = black"

Just to be clear here: "If every cell in the range H4:H25 has a value between 1-5, then font colour in every cell in the range F4:F25 turns black, otherwise it's white"?

ok. Try this:

 


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Hsweep, HFlag As Integer

For Hsweep = 4 To 25
     If Cells(Hsweep, 8).Value > 0 And Cells(Hsweep, 8).Value < 6 Then
          HFlag = 1
          Else
          HFlag = 0
     End If
     If HFlag = 0 Then Exit For
Next Hsweep

If HFlag = 1 Then
     Range("F4:F25").Font.ColorIndex = 1
     Else
     Range("F4:F25").Font.ColorIndex = 2
End If

End Sub

Thank you so much, that seems to work perfectly

You're very welcome.