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

Creatora

New Member
Joined
Dec 27, 2016
Messages
2
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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