If cell font = red, then highlight cell using VBA issues

Question

I have a macro that highlights specific cells using conditional formatting. For some reason it highlights cells without text too. As a workaround I've made it so the just makes the text in the cells red. I was hoping to then add another macro which highlights cells that have red font/text, that way empty cells aren't highlighted like before.

This code makes specific cells bold:

Sub Highlight()
'
' Highlight good values

Application.ScreenUpdating = False

Dim ws As Worksheet, LC As Long

For Each ws In ActiveWorkbook.Worksheets
    LC = ws.Cells(18, ws.Columns.Count).End(xlToLeft).Column

    With ws.Range(ws.Cells(18, 3), ws.Cells(79, LC))
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
                Formula1:="=$C18", Formula2:="=$D18"
            .FormatConditions(Range(ws.Cells(18, 3), ws.Cells(79, LC)).FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Font
                .Bold = True
                .Color = vbRed
                .TintAndShade = 0
            End With

    End With

    Next ws

Application.ScreenUpdating = True

End Sub

This code attempted to highlight cells with red font but it doesn't change the cells colour - it doesn't work

Sub cell_red()

Dim ws As Worksheet, LC As Long

For Each ws In ActiveWorkbook.Worksheets
LC = ws.Cells(18, ws.Columns.Count).End(xlToLeft).Column
    For Each cell In ws.Range(ws.Cells(18, 3), ws.Cells(79, LC))

        If cell.Font.Color = vbRed Then
            cell.Interior.ColorIndex = 44
        End If

    Next

Next ws

End Sub

This may help:
https://answers.microsoft.com/en-us/msoffice/forum/all/conditional-formatting-ignoring-blank-cells/719f087c-5e30-4830-9b7d-0b93cffad357

not sure how to incorporate it though

As mentioned on your other post, add a condition to not highlight the cell if blank — urdearboy
I think this may help, but not sure how to incorporate it into my code answers.microsoft.com/en-us/msoffice/forum/all/… — Zacchini
If cell.DisplayFormat.Font.Color = vbRed Then docs.microsoft.com/en-us/office/vba/api/… You need to use DisplayFormat if what you're looking for is applied via conditional formatting. — Tim Williams

szhep szhep · Accepted Answer · 2020-02-06T12:17:23

Is it absolutely a must that you use Conditional Formatting or are you open to other suggestions too? As i was reading your post (before seeing the code samples), i had something in mind.

I don't like doing Conditional Formatting; a couple small changes to the sheet and the one rule you had created ends up becoming a set of 5-6 rules applying to smaller ranges. Therefore I usually like having a loop do all the formatting for me.

With all that said, let's say we're looping through column A:

For i = 2 to iLastRow (or whatever, 200, etc.)

  [ws].range("A" & i).interior.color = 16777215 'changing it to 'no fill' (in case it was colored before)

  If [ws].range("A" & i) [rest of the condition] Then
        [ws].range("A" & i).Interior.Color = [color no.]
  end if

next i

That .Interior.Color works with .Cells(x,y) as well. Sure, it will not change dynamically with every change to the sheet's contents, but A) i find that this gave me way less headaches than conditional formatting, and B) You can put that formatting loop into a different Sub that your main Sub just Calls, and add a button to trigger only that formatting sub - so you can manually apply your conditional formatting at any time. Or you can add this formatting code into the Sub Worksheet_OnChange.

If cell font = red, then highlight cell using VBA issues

1 Answers