Trouble With Conditional Formatting, Rows, and In-Betweens

So, before we get too far into this, I should say: Yes, I have done searching for this, and no, I haven't found any tutorials, guides, or tips that are helping me sort this out.

I'm working on a spreadsheet (obviously), and I want to conditionally format the entire row based on the data found in one column at the end of the row. Obviously, I had to do custom rules and formulas to achieve this, which I found out after some searching.

The problem is this: nothing is working the way it's supposed to (as far as I can tell).

oyVSx4i.png

The rules I'm trying to achieve are this:

If the H value is between 0 and 1600, I'd like the entire row to be green.

If the H value is over 1600, I'd like the entire row to be yellow.

If the H value is below 0, I'd like the entire row to be red.

Problem is, no order I've tried as made this work, and the only thing is a multi-colored mess.

What am I doing wrong or missing?

Hi Spooky, you can use the INDIRECT formula for this. Do you have a sample workbook that you can attach?

I have the screenshot I provided, and if you can tell me how, I'll attach the workbook file (which is pretty limited to begin with).

Is there any way you could give me an example of what I would need to put in the [FORMULA] section of the Conditional Formatting Rules Manager?

click on "Go Advanced" then "Manage Attachments" and attach that way.

You need to select conditional formatting -> new rule -> use formula -> enter the formula =INDIRECT("H"&ROW())>1600 (for example), enter the format you want.

Then conditional formatting -> manage rules -> applies to enter your range.

for green: =AND($H2 > 0,$H2 < 1600)
but you need to define what if : =0 and =1600

ROW() will return the row number that the formula is in.

e.g. =ROW() in H4 will return 4.


That being said, I would use these:

=$H2<0
=$H2<160
=$H2>1600

instead of building the same thing through INDIRECT.

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.

glad to help. As falcon said, ROW() will return the row number that the formula is in.