Possible to apply conditional formatting across multiple ranges?

hi plasteredric, try below to highlight the smallest number within each range

conditional range = B4:G31

conditional formula = AND(ISNUMBER(B4),B4=SMALL(OFFSET($B$4:$G$31,SUM(ROUNDDOWN((ROW()-ROW($B$4))/10,0))*11,SUM(ROUNDDOWN((COLUMN()-COLUMN($B$4))/6,0)),8,6),1))

thereafter you can create more conditional formula by changing the rank (highlighted in red), good luck!

I am not sure I have the concept. My proposal "apes" what you show.

With a named array constant in Name Manager (TheVals)

Formula:

Please Login or Register  to view this content.

and these in Format Manager:

Apply these to range B4:G11 initially.
Red:

Formula:

Please Login or Register  to view this content.

Tan:

Formula:

Please Login or Register  to view this content.

Green:

Formula:

Please Login or Register  to view this content.

Then select B4:G11 and click Format Painter and apply to B14 and B24.

Originally Posted by FlameRetired

I am not sure I have the concept. My proposal "apes" what you show.

With a named array constant in Name Manager (TheVals)

Formula:

Please Login or Register  to view this content.

and these in Format Manager:

Apply these to range B4:G11 initially.
Red:

Formula:

Please Login or Register  to view this content.

Tan:

Formula:

Please Login or Register  to view this content.

Green:

Formula:

Please Login or Register  to view this content.

Then select B4:G11 and click Format Painter and apply to B14 and B24.

That doesn't seem to give the result i require, i've attached a revised sheet with different numbers and the expected results, might make it a bit clearer.

I need it t work like this,

Cell B4 = 1 (Highlight Red)
Cell B14 = 2 (Highlight Tan)
Cell B24 = 3 (Highlight Green)

Then,

Cell C4 = 2 (Highlight Tan)
Cell C14 = 3 (Highlight Green)
Cell C24 = 1 (Highlight Red)

repeated all the way through to,

Cell G11 = 5 (Highlight Green)
Cell G21 = 1 (Highlight Red)
Cell G31 = 3 (Highlight Tan)

Aha! And yikes! LOL I suspected I was oversimplifying it. This will take me some time.

I couldn't examine the file at the link posted by paulked. I don't have an account there. And oh yes. Please don't cross-post again.

In the attached find these named formulas in Name Manager:
TheDat

Formula:

Please Login or Register  to view this content.

TheVals

Formula:

Please Login or Register  to view this content.

Then find these 3 rules in Format Manager:
Red:

Formula:

Please Login or Register  to view this content.

Tan:

Formula:

Please Login or Register  to view this content.

Green:

Formula:

Please Login or Register  to view this content.

This whole setup depends upon future files maintaining the integrity of Range names format. COUNTIF needs it.

It also depends upon all Ranges sharing only one multi-column range. IE there are no Ranges to the right of these targets.

Originally Posted by plasteredric

I have several ranges of data

Your post does not comply with Rule 8 of our Forum Rules: Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you must provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (most other forums have the same rule).

First, format all with Tan. Then there are 2 criterias left: min and max.
Stay in B4:
1/ Define which range of the cell (range A, B or C):
SUMPRODUCT(--ISTEXT(Sheet1!$B$3:$B4))=1
If stay in B14, it returns 2, because it found 2 cells of text in B3:B14. Stay in B24, = 3

2/ Define distance from B4 to other 2 relation cells then move to get those values:
OFFSET(Sheet1!B4,IF(SUMPRODUCT(--ISTEXT(Sheet1!$B$3:$B4))=1,{10,20},IF(SUMPRODUCT(--ISTEXT(Sheet1!$B$3:$B4))=2,{-10,10},{-10,-20})),)
positive: go down; negative: go up
If active cell is in Range A, go down 10 & 20 rows to get other 2 cells
If active cell is in Range B, go up 10 & down 10 rows to get other 2 cells
...

3/Define MIN value of the other 2 cells:
Use define name:
MinPos
= MIN(OFFSET(Sheet1!B4,IF(SUMPRODUCT(--ISTEXT(Sheet1!$B$3:$B4))=1,{10,20},IF(SUMPRODUCT(--ISTEXT(Sheet1!$B$3:$B4))=2,{-10,10},{-10,-20})),))

4/ Conditional Formating formular for MIN value of cell B4:
=AND(B4<>"",B4=MIN(B4,MinPos))

Repeat step 3 and 4 for Max Value (use name MaxPos)

Hope it helps.