Santa's List: Conditional Formatting Entire Row based columns in another sheet.

[jeffcoleky]

Title says it all. I want to do Format an Entire Row IF the data in Column A of that row contains an exact match to a list of names in another sheet. (we'll use Santa's Christmas list as an example)

I want the people in the "List" sheet, as they are categorized, to determine the row's background color of the names on Sheet 1 (Names). Green Background if they're Nice, Red Background if Naughty.

Names should be able to be added and removed from Sheet 2 at any time.

Sheet 1: "Names"
Bob <- (Entire Row has Green background)
Easter Bunny <- (Entire Row has Red background)
Pat <- (Entire Row has Red background)
Sam <- (Entire Row has Green background)
George <- (Entire Row has Green background)
You <- (Entire Row has Red background)


Sheet 2: "List"

Column A1: Nice
Bob
Sam
George

Column A2: Naughty
You
Pat
Easter Bunny

 

[jeffcoleky]

Really? nobody knows this? i thought it would be an easy question to answer ...

 

[asad]

Try this,

• Define two lists - Nice and Naughty as names using Insert > Name > Define where Nice will point to range that has relevant information and same with Naughty.
• Click on cell B1 in Sheet1 and open Conditional Format
  • IN Condition 1 use

    =OR(Nice=$A2)

    and select the pattern to be green
  • In Condition 2 use

    =OR(Naughty=$A2)

    and select the pattern to be red

Asad

 

[T. Valko]

Really? nobody knows this? i thought it would be an easy question to answer ...

Well, your explanation isn't very clear.

The "entire" row ? Depending on what version of Excel you're using that can be either 256 columns or 16,384 columns. Are you sure you want to format the "entire" row? Or, do you just want to format the cells in the row that actually contain data? That's what people usually want when they say "entire".

How is the data setup on your List sheet? There is no column A1 and A2.

What version of Excel are you using?

 

[jeffcoleky]

Well, your explanation isn't very clear.

The "entire" row ? Depending on what version of Excel you're using that can be either 255 columns or 16,384 columns. Are you sure you want to format the "entire" row? Or, do you just want to format the cells in the row that actually contain data? That's what people usually want when they say "entire".

How is the data setup on your List sheet? There is no column A1 and A2.

What version of Excel are you using?

I do apologize, I did neglect to give you a lot of information...

Entire row meaning cells that contain data.
Excel 2010
Column A and Column B

 

[jeffcoleky]

One more thing. I suppose it would be OK to have it ONLY change the cells to red if the person was under the "Naughty" column in sheet 2. The green is actually just extra since not all names will be on the naughty or nice list initially.

 

[T. Valko]

I do apologize, I did neglect to give you a lot of information...

Entire row meaning cells that contain data.
Excel 2010
Column A and Column B

OK can you be more specific about this:

Entire row meaning cells that contain data.

How many columns in the row will contain data?

Column A to D? A to J? A to AA?

 

[asad]

Did you try my suggestion?

I tried it and it comes like this

Excel Workbook
	A	B	C	D	E	F	G
1	Bob
2	Easter Bunny
3	Pat
4	Sam
5	George
6	You
7
Sheet1

 

[jeffcoleky]

Did you try my suggestion?

I tried it and it comes like this

Excel Workbook
	A	B	C	D	E	F	G
1	Bob
2	Easter Bunny
3	Pat
4	Sam
5	George
6	You
7
Sheet1

I had the same result EXCEPT mine shows George as being naughty. How Strange?! I did exactly what you said too... and he's in the right column.

 

[jeffcoleky]

Oops, I had headers in the "list" sheet...duh me...

so this is basically the idea. However, i want it to automatically highlight the row, not just the one cell.

Currently, the columns range from Column "A" thru Column "AG" but that may get wider over time. In addition, those columns have other conditional formatting in them.