Conditional formatting a Range

[pingme89]

I would like to format a range of cells in Column "B".
I would like to use VBA to set the conditional formatting however, each cell will have slightly different formulas.

For example. I want to change the color of the font for each cell that has a value of 0.

So if B2=0, then the font in B2 will be white.

I don't know how to make a blanket conditional formatting statement for a range of cells all at once without all cells having the same formula.

B12 = 5 , B2 =0. If I apply a conditional format to all cells in column B based on the value of one cell.

How can I resolve this issue? Any help would be appreciated.

 

[Peter_SSs]

Your requirement is not clear to me, so I'm not sure whether it is possible or not.
Can you give a larger sample of data (say 8-10 rows) and explain which cells should be formatted in what way and why?

 

[pingme89]

So I will check the number of row in B with values.
I want to conditionally format each cell in B starting with B13 all the way to Bx where x is the number of used rows in B. Let's just assume there are 20 rows with values in Column B.

If B13 has a value of 0, then the font will be white. If B14 is a non-zero, then the color of the font should be black. If B15 = 0 then the font in B15 will be white etc...

is there a way to assign each conditional format with one statement or do I need to do it in a loop?

 

[Peter_SSs]

OK, so you want, from B13 down
- white font if the cell contains 0
- black font otherwise.

No need for a loop.
Format normally, the whole of column B with black font. (For most people that is the default font colour)
Run the following code (in a copy of your workbook) to test.

Sub CF_Col_B()
  With Range("B13", Range("B" & Rows.Count).End(xlUp))
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    .FormatConditions(1).Font.Color = vbWhite
  End With
End Sub

 

[pingme89]

Thanks. When I ran the code the first time, it didn't work. When I ran the code a 2nd time it did. Is there a reason for that? I appreciate all your help. Without your solution I was going to run a loop which I am desperately trying to avoid to minimize clock cycles and minimize execution time. In fact, I duplicated your code back to back so it ran twice. Is that necessary? Is Excel bugged?

 

[Peter_SSs]

There should definitely be no need to run the code twice. I don't know why that would be happening.
Is the code being run as part of a larger code?

 

[pingme89]

Yes it is part of a bigger chunk of code.

 

[Peter_SSs]

Yes it is part of a bigger chunk of code.

In that case my first suspicion for the code not working first time would be that it is related to the rest of that larger code (or any other code - eg Event code - that is used in the workbook). Is it possible/feasible to see the larger code and any 'event' codes that you have in the workbook?

 

[pingme89]

I am not sure what an event code is. I don't think I have any of those in my code.

 

[Peter_SSs]

I am not sure what an event code is. I don't think I have any of those in my code.

OK, but what about the "bigger chunk of code" that the conditional formatting code was embedded in?