Conditional formatting a Range

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
170
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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?
 
Upvote 0
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?
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
Solution
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
I am not sure what an event code is. I don't think I have any of those in my code.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top