Hi gootroots,

Since you cannot reference another sheet with conditional formatting what I
do is set up a set of hidden cells. On sheet 2 write a small "if" formula
referencing sheet 1 that will say "yes" when your criteria is met and and
"no" when it is not. You can then reference those cells to change your
colors.

Make sense?

Squeaky

"gootroots" wrote:

I need formula to highlight cells based on values contained in another sheet
in the same workbook.

Sheet1
A1 = Product Name
A2:A100 contains unsorted unique values
B1:Q1 contains unsorted unique headings
B2:Q100 have sporadic cells containing a value (e.g. D3, F18, G22, J86...
etc)

Sheet2
A1 = heading - Product Name
A2:A100 contains unsorted unique values
B1:Q1 contains unsorted unique headings
B2:Q100 its irrelevant if any cell contain a value

I need a conditional format in sheet2 B2:Q100 to search in sheet1 B2:Q100
and highlight any cells containing a value

For example:

The value "Tractor" can be found in sheet1 A43 and also in sheet2 A23

The heading "PartNo" can be found in sheet1 D1 and also in sheet2 H1

A formula locates and matches both "Tractor" and "PartNo" in sheet1

Any value found to be in Sheet1 B43:Q43 will then have the relevant cells in
sheet2 B23:Q23 highlighted