How to change color of a cell based on value of cell from another page of worksh

Question

How to change color of a cell based on value of cell from another page of worksheet ?

I want to change the color of the cell if the data is matching from table available at page 2 of excel sheet.

Basically we have data fields which have address with zip codes, some zip codes are for regular delivery zones, and some zip codes are for extended delivery zones which require a shipping surcharge, and we want to highlight the shipping surcharge zip code fields,

on page 2 of the sheet we have a list of extended delivery zone zip codes.

can anyone suggest how to implement this ?

Explanation / Answer

In Excel 2007 you have probably already discovered that you cannot directly reference a cell or range of cells from another worksheet. However, the workaround for this is to assign a defined name *Formulas, Defined Names, Name manager) to the second worksheet's relevant data and you can reference the defined name in a conditional formatting rule for the first worksheet.

Off the top of my head, I'd advise you to name the column of extended delivery zone zip codes on Sheet2 as something like lst_ExtDevZoneZips. The conditional formatting for Sheet1 could be based upon something like one of the following formulas,

=COUNTIF(lst_ExtDevZoneZips, $D2)

... or,

=ISNUMBER(MATCH($D2, lst_ExtDevZoneZips, 0))

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.