Conditional Formatting Issues...

[Dink37797]

Hi All,

I am having some issues with Conditional Formatting. I want to get a range of cells to be in colour when a separate range of cells' values are within two other cells. I.E I want F4:AX4 to be in colour when the range F3:AX3's values are between the values in cells D4 and E4. The current rule i have worked out is; =IF(AND($F$3:$AX$3>=MIN($D$4),$F$3:$AX$3<=MAX($E$4)),TRUE,FALSE)
But this doesn't seem to be working!

Please can anyone advise?!

Thanks in advance

[Roel Jongman]

you need to put the max and min around the ranges and not around the single cells
also the if statement is obsolete. the AND formula is also a boolean function that outputs TRUE or FALSE that will trigger the CF

Please Login or Register  to view this content.

If you need further assistance please upload a sample file so it is easier to show and explain in the sheet

[Dink37797]

Hi there,

Still not working unfortunately. I have attached a sample work sheet to hopefully better explain what I mean. Thanks

[Pete_UK]

Is the attached file what you are trying to achieve?

I used this rule for F2:AX2:

=AND(F$1>=$D2,F$1<=$E2)

then used the Format Painter to apply the rule successively to the following rows (having deleted the rules that were in there first).

Hope this helps.

Pete

[Dink37797]

Unfortunately that didn't seem to work on my live spreadsheet. Ideally I want to have one rule for the whole row and then just alter each row rule to reference the right cells..

[Pete_UK]

You select all the cells in one row, from F2 to AX2. Then you click on Conditional Formatting and enter the CF formula as if it was just for the first cell in the highlighted range, being careful where you put $ to anchor the row/column. When you click Apply or OK, Excel will adjust the actual formula for each cell in the range, so you only need to put it in once.

Hope this helps.

Pete

[Dink37797]

I got it to work Thank you!

[Pete_UK]

That's good to hear.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete