Conditional formatting a Range

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
130
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 23, 2014
Messages
130
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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:

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
130

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 23, 2014
Messages
130

ADVERTISEMENT

Yes it is part of a bigger chunk of code.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 23, 2014
Messages
130
I am not sure what an event code is. I don't think I have any of those in my code.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,531
Messages
5,529,389
Members
409,870
Latest member
Well59
Top