Cell highlighting question



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

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...

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



Bernard Liengme

If all you need is to locate on B2:Q100 of Sheet2 any value that appears in
the same range on Sheet1 without reference to the row and column headers,
then use
Formula Is =COUNTIF(mydata,C2)>0
Conditional Formatting does not allow you to reference a range in another
sheet, so you must first select B2:Q100 in Sheet1 and type a name like
MyData into the name AND PRESS ENTER.

If this does not answer the question please tell us if Tractor on the same
row and PartNo in a different column are part and parcel of the requirement.

best wishes


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

Make sense?



Thank you guys for the help

I have followed your advice Squeaky and will use hidden cells.
The following formula in D23 (the hidden cell) returns a True or False
result for a Product match

Match Found",A23),A23)

I need to know what formula is needed to reference a formula result when
using conditional formatting. Cell E23 will change colour if D23 is True.




In cell E3, select conditional formatting (or Manage Rules in 2007). In the
formula statement put =D3=true. select your color formatting.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question