# Conditional Formatting Referencing Different Worksheets

This is a discussion on Conditional Formatting Referencing Different Worksheets within the Excel Questions forums, part of the Question Forums category; Hi: I am currently using VLOOKUP with cells in the range \$A\$10:\$D\$131. I need the cells to return BLUE if ...

1. ## Conditional Formatting Referencing Different Worksheets

Hi:

I am currently using VLOOKUP with cells in the range \$A\$10:\$D\$131. I need the cells to return BLUE if they are from A10:A47, GREEN if they are from A49:86, and RED if they are from A108:120. I tried Conditional Formatting using the menu, but it said I couldn't reference other worksheets (plus I wasn't sure if I had the formula correct for referencing text). I'm not real familiar with VB so if an explanation requires that, it needs to be thorough and detailed....

Jeff

2. ## Re: Conditional Formatting Referencing Different Worksheets

WELCOME TO THE BOARD!

In order to reference ranges found on other worksheets in Conditional Formatting, you need to name your range and reference it that way.

To name a range, highlight the range, then from the Insert drop down menu, select Name, then select Define. Then give your range a name, like "MyRange".

Then, to reference this range, instead of using something like A1:A100, simply use MyRange.

3. ## Re: Conditional Formatting Referencing Different Worksheets

I still don't have it quite right. For the conditional format I have:

=(A21=Data1) ...make font blue
=(A21=Data2) ...make font red

...Data1 and Data2 are the names of my cell ranges (\$A\$10:\$A\$47). This only works though if A21=the first cell in Data1. What do I use if I want this to be true if A21= ANY cell in Data1?

Thanks,
Jeff

4. ## Re: Conditional Formatting Referencing Different Worksheets

Try the following conditional formatting:

If you're range is A10:D131,

Then in conditional formatting block 1, select
Formula is: =MATCH(F10,\$A\$10:\$A\$131,FALSE)>=99
Font color: Red

In conditional formatting block 2, select
Formula is: =MATCH(F10,\$A\$10:\$A\$131,FALSE)>=40
Font color: Green

In conditional formatting block 1, select
Formula is: =MATCH(F10,\$A\$10:\$A\$131,FALSE)>=1
Font color: Blue

MATCH returns the array position. Since you are starting at Row 10, Row 10 marks the first position. So the difference between the row number and position number is 9. That is where the values come form (108-9=99, 49-9=40, 10-9=1)

5. ## Re: Conditional Formatting Referencing Different Worksheets

I'm sorry, I forgot that your range was on a different page. If you have named the range "MyRange", then in all three match formulas, change:

\$A\$10:\$A\$131

to

MyRange

So the first one would look like:
=MATCH(F10,MyRange,FALSE)>=99

6. ## Re: Conditional Formatting Referencing Different Worksheets

That worked well. Thanks for the help!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•