Microsoft Excel - VBA question in Excel regarding shading a range based on an if-then condition

I have a workbook that has a master data sheet, then three other worksheets I want to compare the data with.  I want to do a look up of the asset # from sheet 1 and if it exists in sheet 2, 3, or 4, compare the customer #.  If the customer number and asset # match any of the other sheets, I want the row in sheet 1 to highlight green.  If the asset # and customer # in sheet 1 do not match where it was found in the other sheet, the row to be highlighted red on sheet 1.  If the asset # from sheet 1 is not duplicated on any of the other sheets, then highlight it yellow on sheet 1.

I am thinking VBA is the best way to accomplish this, but I am not sure on the syntax.  Any help or direction is appreciated.

Hello B,
Are the Asset numbers and Customer numbers in a single column (each) or are they scattered throughout the sheet?  Also, will they be adjacent ie in consecutive columns?  An example of the sort of layout on each page would be handy.
Regards
Harry

Hi,

Here some suggestion.. something like this...
sub hilightRowWithCond()
for each assetNum in sheets(1).range("A2:A1000")   'suppose the range of assetNo is A2:A1000
if worksheetfunction.countif(sheets(2).range("A2:A1000"),assetNum) then
assetNum.entirerow.interior.color=65535    'color that you want
elseif
if worksheetfunction.countif(sheets(3).range("A2:A1000"),assetNum) then
assetNum.entirerow.interior.color=NNNNNN    'color that you want
else
assetNum.entirerow.interior.color=NNNNN   'color that you want
end if
end sub

something like this..


Hope this help.

Pichart Y.

Hello B,

bchaj.zip

This file has an implementation using helper cells and no VBA.  I think it does what you want, assuming that the data layout is as simple as I have assumed.  It would be possible to do away with the helper cells but the formulas would be rather long and messy.
Regards
Harry