I have a spreadsheet that contains progress data in the range 0 to 1. The
data is populated from scratch from Access using VBA. I want cells to be
red if the number in the preceding column is lower, but I want to apply this
formatting from Access.

I've got an idea that the code will be some sort of loop but have no idea
what the syntax might be. Anyone done this or similar?

Many thanks.

Keith.

If you highlight the first cell with a value (or the second one since
you'll be referencing the cell to the left) and go into Format -
Conditional Formatting.
In the dialogue box where it says "Cell Value is" change to "Formula
is", then in the other box write "=(c10<b10)" [this is for cell c10,
change accordingly for whichever cell you're in]. Then hit the Format
button and change the colour of the cell under the Patterns tab.
The formatting should be in place for this cell. Now Copy the cell and
Paste Special - Formats across the rest of the range (except for the
first column).

Be careful when your VBA imports the data from Access that you paste
only the values and don't overwrite the formatting. In your VBA it
will be something like Range("C10").Value = [value from Access] rather
than Range("C10") = [value from Access]. I'm not sure how you're
pulling this in, but this should give you a starting point.

"Erasmus" wrote in message
oups.com...
If you highlight the first cell with a value (or the second one since
you'll be referencing the cell to the left) and go into Format -
Conditional Formatting.
In the dialogue box where it says "Cell Value is" change to "Formula
is", then in the other box write "=(c10<b10)" [this is for cell c10,
change accordingly for whichever cell you're in]. Then hit the Format
button and change the colour of the cell under the Patterns tab.
The formatting should be in place for this cell. Now Copy the cell and
Paste Special - Formats across the rest of the range (except for the
first column).

Be careful when your VBA imports the data from Access that you paste
only the values and don't overwrite the formatting. In your VBA it
will be something like Range("C10").Value = [value from Access] rather
than Range("C10") = [value from Access]. I'm not sure how you're
pulling this in, but this should give you a starting point.


Many thanks. I didn't realise there was already a thread with this name and
it took me ages to find it ;-)

What I really want is to have the formatting set using VBA in Access. I
need to be able to output my data to any Excel file so pre-formatting a
specific file isn't an option. Is this do-able?

Regards,
Keith.