Conditional formatting when not knowiing the last row and getting it to color the right row

[Creatora]

I have an Excel sheet with some data, I never know how many debs/loan the person have. It could be all from one to two hundred or even more...

I'm trying to look in column I if the cell has N/A in it, if so set the font to be in red, if not, it should be white.
If I try this code: =$I4="N/A" I' get 4 in red, not the actual row that has N/A in it, as it is now, it should be row 7 that gets the red font.

How can I solve that problem? I'm not really feel like doing a new rule every time the user adds a post.
As it is now I have a listener witch triggers and macro to put in the formulas into the proper cell and row.

 

[Joe4]

Welcome to the Board!

You can set Conditional Formatting up for a whole range at once. So, select a range that will always extend beyond the last row a person may use.
For example, highlight cells I1:I1000 (or whatever range works for you).
Then, with that entire range selected, write the Conditional Formatting formula as it applies to the FIRST cell in that range. If you use your absolute/relative range references in your formula correctly, Excel will automatically adjust the formula for the other cells in your range.
So your formula would look like: =$I1="N/A"

 

[Creatora]

Thank you!

It seems that it was the I4 reference that caused the hick up. Now it works like a charm.

Now I have: =$E:$H as reference and =$I1="N/A" in the formula.

 

[Joe4]

You are welcome!

Yes, the key is to make sure that formula you write and the cells it references reflects the first cell/row in your highlighted range (and uses absolute/relative cell references correctly to reflect what you need it to do). Otherwise, things won't match up correctly, and you will get unexpected results.