change cell color only if that cells font color is black and it's value is < 2000

Originally Posted by randall78

Hi'
I am trying to use vba to change the color of a cell only if that cells font color is black and it's value is less than £2000.

Sub Button1_Click()
If Range("Table1[Balance]").Font.ColorIndex = 1 And Range("Table1[Balance]").Value > 2000 Then
MsgBox ("TEST")
End If
End Sub

Less or more?

Sorry for double post.
How can I delete this one?

change for values less then 2000 but only if font color is black.

I should also point out that the calls in the Balance column of the table contain a formula to return the value. Don't know if that make a difference.

If your "Table1[Balance]" cell is in a named range in a list object then the syntax would be

If ListObject.Range("Table1[Balance]").Font.ColorIndex = 1 And ListObject.Range("Table1[Balance]").Value < 2000 Then
MsgBox ("TEST")

If Range("Table1[Balance]").Font.ColorIndex = 1 And Range("Table1[Balance]").Value > 2000

If that cells font color is black and it's value is less than £2000.

Compare what you saying and what you've got in code.

Hi,

Not sure I understand your syntax.

Table1[Balance] refers to an entire column of values within the table, not just a single cell.

Are you looping through that column? If so, can we see the rest of your code?

Regards

Put in "Table1[Balance]" value 5 000 (in black font), run it and tell what you got.

try (just hint):

Sub Button1_Click()
Dim i As Integer
For i = 5 To 33
    If (Range("E" & i).Font.Color = vbBlack And Range("E" & i).Value < 2000) Then
        MsgBox ("In cell E" & i & " value " & Range("E" & i).Value & " is black and less than 2000")
    End If
Next i
End Sub

    If (Range("E" & i).Font.Color = vbBlack And Range("E" & i).Value < 2000) Then
        Range("E" & i).Font.Color = vbRed
    End If