Conditional Formatting Referencing Different Worksheets

jmornhi

New Member
Joined
May 28, 2003
Messages
3
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....

Thanks for your help!
Jeff
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top