Change font color for certain range of cell value using VBA

[dave tan]

Hi there.
I am trying to make a certain range of cell (A1:C5) turn to red/white according to the value of A in each row using VBA
by clicking on a button to check and change it.

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Dog[/TD]
[TD="class: xl65"]200[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Pig[/TD]
[TD="class: xl65"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]R1[/TD]
[TD="class: xl65"]Dog[/TD]
[TD="class: xl65"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]F1[/TD]
[TD="class: xl65"]Dog[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]Pig[/TD]
[TD="class: xl65"]2
[/TD]
[/TR]
</tbody>[/TABLE]

Rule: If the Value of A is not a number then turn the color of the row to Red/white.
In this case:
Range A3:C3 and range A4:C4 in red/white color.

Thanks,
Dave

 

[tyija1995]

Hey try this code:

Sub ChangeFontColour()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("A1:A5")
    For Each cell In rng
        If IsNumeric(cell) = False Then
            Range(Cells(cell.Row, Columns("A").Column), Cells(cell.Row, Columns("C").Column)).Font.Color = vbRed
        Else
            Range(Cells(cell.Row, 1), Cells(cell.Row, 3)).Font.Color = vbWhite
        End If
    Next cell
    
End Sub

Then make a commandbutton and use the code ChangeFontColour to link the macro.

 

[tyija1995]

On second thought, when you say red/white do you mean red font and a white background?

Use this code instead:

Sub ChangeFontColour()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("A1:A5")
    For Each cell In rng
        If IsNumeric(cell) = False Then
            With Range(Cells(cell.Row, Columns("A").Column), Cells(cell.Row, Columns("C").Column))
                .Font.Color = vbRed
                .Interior.Color = vbWhite
            End With
        Else
            With Range(Cells(cell.Row, Columns("A").Column), Cells(cell.Row, Columns("C").Column))
                .Font.Color = vbBlack
                .Interior.ColorIndex = 0
            End With
        End If
    Next cell
End Sub

 

[dave tan]

Hi tyija1995,
Thank you for the code. It works like a charm.
There is another issue. After i click the button, all the command in excel turn grey until i click anywhere on the sheet.
How to make the command automatically finish without clicking again?

Thanks,
Dave

 

[tyija1995]

Hey,

I'm not sure how to fix that with a commandbutton actually(?)

However you can insert a shape of your choice and assign the ChangeFontColour macro to the shape - then you can change the text / format of the shape too, it's more customizable than a commandbutton!

Insert Ribbon tab -> Shapes -> Choose a shape -> Right click shape -> Assign Macro -> ChangeFontColour -> OK

 

[dave tan]

Hi tyija1995,
That's really good idea. Two thumbs.
Thanks again,
Dave

 

[tyija1995]

No problem glad I could help :D

 

[Rick Rothstein]

Assuming A1:A5 are constants (not formulas), here is another macro to consider...

Sub ChangeFontColour()
  [A:C].ClearFormats
  With Intersect([A:C], [A1:A5].SpecialCells(xlConstants, xlTextValues).EntireRow)
    .Font.Color = vbRed
    .Interior.Color = vbWhite
  End With
End Sub

 

[dave tan]

Hi Rothstein,
That works too.
Thank you for the new idea of coding,
David