column shading based on cells value - Help Please

[c0ok]

Is there a way to automatically shade or fill colour an entire column in Microsoft Excel, based on the value found within a cell? Its basically for an automatic report generator I am working on, and I would like certain columns automatically highlighted based on a cell's value.

Any help that could be provided would be greatly appreciated........

 

[xlbo]

have a look at conditional formatting

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[c0ok]

thanks for the suggestion, but it looks as though the conditional formatting only allows for 3 criteria.

There are actually 12 different colours by which the row could be highlighted, based on the value of or combination of values found within 2 particular columns of each row. Is there an easy way to get around this? If I were to create an if statement within the worksheet_change submodule, how would I reference the particular cells values?

 

[xlbo]

entire COLUMN or entire ROW ???
You have used both
As a general point, yes, you can quite happily use the change event to process this
You can access the cell that has chenged by using the keyword
TARGET

so you can get Target.row, Target.column, Target.address, Target.value etc etc
Given that most people highlight rows, I'll use that as an example:

in the worksheet change event


mRow = target.row
if cells(mRow,colCheck1).value = 1 and cells(mRow,colCheck2).value = 2 then
rows(mRow & ":" & mRow).entirerow.interior.colorindex = 3
elseif cells(mRow,colCheck1).value = 2 and cells(mRow,colCheck2).value = 3 then
rows(mRow & ":" & mRow).entirerow.interior.colorindex = 4
elseif......etc etc
end if

where colcheck1 is the 1st column to check the value of and colcheck2 is the 2nd - hope you get the idea

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde