EXCEL: Conditional Formatting Using Row Not Working

Question

I have an Excel Sheet that I need to conditionally format so I can tell how close numbers in the cells are to the midpoint of a range. These are the rules:

Blanks = WHITE

Values outside of my desired range (Rows B and C) = RED

Values inside of my desired range will scale with the midpoint = GREEN and the outer points = YELLOW

Format Rules

If I make a separate condition on every row, it is CORRECT and looks like this:

Correct

But that would take an insane amount of time to create rules for every row.

If I make the following formula to try to speed things up, it calculates correctly for the first row and then incorrectly for the remaining rows (only using the numbers from the first row).

3-Color Scale Example:

Minimum =INDIRECT("B"&RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW())))

Midpoint =(INDIRECT("B"&RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW())))+INDIRECT("C"&RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW()))))/2

Maximum =INDIRECT("C"&RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW())))

Incorrect:

Where are rows B and C in your examples? Do both screen captures start at ROW A? It looks like that's can't be the case since I assume "Normal" is not one of your desired values. Can you also edit your question with the conditions you use for a single row? — cybernetic.nomad
– cybernetic.nomad, Commented Aug 9, 2018 at 20:14
You realize your first format rule applies to the range =$E$3:$O$56 and the other two rows apply only to $E$3:$O$3? — PeterT
– PeterT, Commented Aug 9, 2018 at 20:17
The Range is Rows B and C. So for example, B3=1.1 and C3=2.5. Row 3's numbers are supposed to be based on those. Yes, the first rule just White's out all blanks and does not need to be applied row by row. — Ashkaan
– Ashkaan, Commented Aug 9, 2018 at 20:24

teylyn · Accepted Answer · 2018-08-13 20:33:24Z

0

Color scales don't accept relative references in the value formulas. You'll have to bite the bullet and use absolute references and adjust the row number for each row.

edited Aug 13, 2018 at 20:33

answered Aug 9, 2018 at 20:51

teylyn

36.3k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashkaan Over a year ago

I'm glad that you understand the question perfectly. Are you sure that there's not some clever formula that can get this accomplished?

teylyn Over a year ago

Sorry, I don't think that Excel can do this with the out of the box color scale feature. You may want to employ VBA to automate the formula adjustments for each row.

Collectives™ on Stack Overflow

EXCEL: Conditional Formatting Using Row Not Working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related