How to preserve conditional formatting on a web query table result

[Guest]

I have an external database that Excel queries and returns two columns of
dates. I can set up a conditional format (in one colum) so that the dates in
each row of the column change colour if the corresponding columns date is
different. My problem is how to COPY and PASTE the conditional formatting
across all dates in the one column (so that each cell looks at the date in
the corresponding cell next to it)?

Any ideas or suggestions?

 

[Peo Sjoblom]

You can just use the format painter to copy and paste formats

 

[Guest]

Select entire Col "A" (assuming 1st date in Col A) click on conditional
formatting select "Cell Value Is", "not equal to" "=$B1"

that should do it.

 

[Guest]

Thanks Peo but the Format Painter does not 'paint' across 'conditional
formatting'

 

[Peo Sjoblom]

Yes it does, you only have to use the correct formula in the conditional
formatting,
what formula are you using?

 

[Guest]

Thank Arun but it seems that I have to specify that all cells must compare
against B1 in your example. am i missing something?

Example:
Project Name PlannedDueDate ActualDueDate
Project1 10/10/05 10/10/05
Project2 10/11/05 15/11/05
Project3 10/11/05 10/11/05

Here I would like actualduedate for Project 2 to change colour since it is
running late compared with the plannedduedate in Project2. Hope this makes
sense

 

[Guest]

Thanks Peo
Formula is... Condition 1 - Cell Value Is greater than =?????
(where =???? is the cell next door to itself)

 

[Peo Sjoblom]

use formula is instead of cell value, i.e.

=B2>A2


now if you copy that (format painter) it will adjust so if you copy it down
it will change to

=B3>A3

across

=C2>B2


if you use cell value you need to use relative references A2 as opposed to
$A$2 (it will default to absolute if you click on the cell)

cell value greater than A2

 

[Guest]

Brilliant. It works!!! thanks Peo

use formula is instead of cell value, i.e.

=B2>A2


now if you copy that (format painter) it will adjust so if you copy it down
it will change to

=B3>A3

across

=C2>B2


if you use cell value you need to use relative references A2 as opposed to
$A$2 (it will default to absolute if you click on the cell)

cell value greater than A2


--

Regards,

Peo Sjoblom

 

[Guest]

simon, select the cell heading PlannedDueDate and select the Entire column,
and invoke Conditional formatting. and enter the options as i said earlier.
as i've specified to validate with "$C1" excel applies $C1 for 1st and
automatically changes the formula to $c2, $c3 and so on...