Conditional Format based off of a Name Range? (1 Viewer)

[sdzc]

Is it possible to conditionally format based on a range named either in the same worksheet or in another worksheet?

For example, if John Doe is entered in a cell on Worsheet1, can the cell be highlighted if the name John Doe is a part of the range of "Vendor"?

 

[PaulJR]

I'm sure the answer is no. But a workaround that might work is to insert a formula into a new column in Worksheet1 as follows:

=COUNTIF(A1,Vendor!A:A)

This assumes John Doe is entered in A1 in worksheet1, and is matching it aganist any John Doe's that might exist on a separate worksheet (Vendor) in column A.

Now you can do your conditional formatting as follows:

Condition1: =$B$1>0
Condition2: =$B$1=0

 

[Brianwarnock]

I think that should be
=COUNTIF(Vendor!A:A,A1)
but he could use his named range
=COUNTIF(Vendor,A1)

BTW you cannot use references to another worksheet in conditional formatting, however if you are on the same worksheet then =(Countif(Vendor,A1)>0) will work in the condition.

Brian

 

[sdzc]

Thank you both for your responses. To simplify things, I moved the range to the same worksheet and set a print area so that info would not print.

I was able to get it to highlight the single cell based on conditional formatting, thank you!

The next step is to get it to apply the formatting when a name from the vendor range shows up in any of the cells in B2:K21.

If I cannot get this to work, I will have to do the conditional formatting for each cell individually, which would not be fun.

Thanks again!

Edit, I have multiple conditional formatting working in one cell (here is the formula: =(COUNTIF(Relief,$B$2:$K$21)>0) which applies to cell $b$2

I cannot get it to work when I highlight an area to be conditionally formatted (example b2:k21). I tried without the "$"'s and it did not seem to work that way either.

Sorry if this is getting confusing.

 

[sdzc]

I was able to figure it out, but only in a manual sense.

I did a copy/paste special for the formats, but I have to manually change the =(COUNTIF(Relief,$B$2:$K$21)>0) formula for the $B$2 to the location of the cell I want to conditionally format. the problem with this is that I have a range of cells (B2:2:K21) that I want to do this in with 8 possible formats (cell colors) depending on what is entered.

As I said, it works fine, but if anyone has a faster way to do it, I am all ears.

 

[sdzc]

Figured it out.

Needed to take the $ out of the formula: =(COUNTIF(Relief,$B$2:$K$21)>0)

Actually changed the formula to: =(COUNTIF(Relief,B2)>0) with the location portion remaining as $B$2. This allowed me to copy and paste one cell at a time and the formulas changed automatically to adjust for the cell it was being pasted into.

Not perfect, but it worked and a lot faster than changing the cell reference within each of the 8 different Conditional Formats for each cell.

Thanks again for the responses!

 

[Brianwarnock]

I was asleep in bed whilst you were working your way through this. You are correct about removing the absolute referencing, but once you had done that all you needed to do was use the format painter to drag the original cell across the area required.

Brian

 

[sdzc]

I tried that and it did not work for some reason. The only way I could get it was to copy paste each individual cell.

Don't know why that would have been an issue with it.