Conditional Formatting - Simple

[coloradoprincess78]

There is something simple that I am missing here, but can't take any more time googling it

Applied conditional formatting to Column I to change font color to white when Adjacent Cell in Column H has no value. H contains a formula.

Conditional formatting formula is =$H2=" "

Here is what it is doing, and it sort of works - for some reason on my screen I16 is white. And will not turn to black until i have something in H17 - which is slightly counterproductive.


Excel 2010

	H	I

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]State Abbreviation[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Number[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]0100[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]0101[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]0102[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]0102[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]0103[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]ND[/TD]
[TD="align: center"]0100[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"][/TD]

</tbody>

Site Numbering​

Any suggestions are greatly appreciated, I've tried 10+ different formulas already , but this is one is the close as I can get it. Thanks in advance!

 

[C with no eyes]

Change it to $H1=""
i.e. without the space between speech marks.

 

[Joe4]

Let me guess. You highlighted ALL of column I, and then applied the formula: =$H2=" ", right?
If you did, you are off one row.

The rule is that when applying Conditional Formatting to a whole range of cells (instead of a single cell), you need to write your formula as it applies to the very first cell in your selection.
So, if you highlighted all of column I, your first cell is cell I1, not I2.

If you want to exclude the first row, you have a few options:
1. Start your selection in Row 2 (meaning you cannot select the entire column)
- or -
2. Add a row component to your formula, i.e. =AND($H1=" ",ROW()>1)
- or -
3. Select the whole column, write your formula like: =$H1=" ", and after applying, highlight cell I1 and remove the Conditional Formatting from that cell.

Actually, in this instance, since you actually have titles in row 1, you should be fine to highlight all of column I and use the formula: =$H1=" "


Edit: And note the reply above mine. A single space and nothing (blank/empty cell) are NOT the same thing.

 

[coloradoprincess78]

Thank you Joe! I knew it was simple.. (face palm) I very much appreciate you taking the extra time to explain it to me in more detail.
And yes, the formula with no space doesn't work, although I'm not sure I understand why haha...
But again, thanks for you help!