Conditional Formatting VBA

[cart0250]

Good day,

I'm trying to apply conditional formatting to range B2:B500. If the cell value is NOT equal to "A" and If the cell is NOT blank, apply a gray interior (color index 15).
The below works, but only for the used range within the specified range. So if user subsequently adds data to one of the blank cells, the conditional formatting is not applied.

Can anyone help modify this so the conditional formatting will also be applied based on any new data the user enters into the blank cells?

I've been playing around with only applying the formatting if the value is not equal to "A" and if cell length is not equal to 0 (instead of blank)... but no luck yet.

Appreciate any help.

Sub applyCF

Dim cond As FormatCondition
Dim rng As Range
Dim ws As worksheet

            Set rng = ws.Range("B2:B500").SpecialCells(xlCellTypeConstants)
            Set cond = rng.FormatConditions.Add(xlCellValue, xlNotEqual, "A")

            With cond
                .Interior.ColorIndex = 15
            End With
End Sub

 

[Joe4]

Why not just apply the Conditional Formatting to every cell in your range, and just include logic in your rule in determining which cells to be turned gray.
If you use the "Formula" option of Conditional Formatting, your formula might look something like:

=AND(B2<>"A",B2<>"")

To get the VBA code for that, you can simply turn on your Macro Recorder and record yourself creating the Conditional Formatting rule (the Macro Recorder is a great tool to get snippets of coding like this!).

 

[cart0250]

Thanks... i was able to get it working as below.

Sub applyCF

Dim rng As Range
Dim ws As worksheet

Set rng = ws.Range("B2:B500")           
  rng.FormatConditions.Add Type:=xlExpression, Formula1:= "=AND(B2<>""A"",B2<>"""")"
  rng.FormatConditions(1).StopIfTrue = False
  rng.FormatConditions(1).Interior.ColorIndex = 15

End Sub

 

[Joe4]

You are welcome.