Colour multiple cels using range..

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adhide - 14 Mar 2008 03:51 GMT

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.

Reply to this Message

Jim Cone - 14 Mar 2008 04:32 GMT

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.

Signature

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.

Reply to this Message

Max - 14 Mar 2008 04:34 GMT

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 A2:D100,
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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message