Colour multiple cels using range..

[adhide]

I am aware of conditional formatting, but what I want to be able to do is
colour multiple cells on a single row based on the 1st cell (being a date)
equalling a date in another work sheet, as conditional formatting only allows
the function to work on the worksheet that the function is placed in.

 

[Jim Cone]

Maybe a column of helper cells on the sheet to be colored with a formula...
=A5=OtherSheet!A5 '(returns True or False)
And use the helper cells to activate the conditional formatting.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"adhide"
wrote in message
I am aware of conditional formatting, but what I want to be able to do is
colour multiple cells on a single row based on the 1st cell (being a date)
equalling a date in another work sheet, as conditional formatting only allows
the function to work on the worksheet that the function is placed in.

 

[Max]

Using INDIRECT is one way

Example, suppose you want to CF cols A to D in Sheet1
if the dates in col A are found in Sheet2's col A

In Sheet1,
Select the range to be CF'd, say, select A2100,
then apply the CF using Formula Is:
=ISNUMBER(MATCH($A2,INDIRECT("'Sheet2'!A:A"),0))

Note that the "$" in $A2 will tie the CF for cols B to D to the date in col A