Conditional formatting a range

[WLMPilot]

This is a 3 part question. I have a workbook with several sheets. The one I
am concerned with is PCEMS. This sheet has a chart for each year beginning
with 2006. I want to change the color of two rows when the workbook is
opened. This will be done via conditional formatting using VBA. The two
rows represent the current payperiod.

Within each chart are 52 weeks (ie 52 rows not counting headers, etc). I
can figure out how to locate the correct chart based on year and then
payperiod, I just need to figure out the following:

1) How to execute macro when workbook is opened?

2) What is the code to change the color for the cells?

3) Can an entire range be changed at once or do I have to change each cell
one at a time?

Thanks,
Les

 

[FSt1]

hi,
you have confused me.
first you don't need vba to set conditional formating.
if you are going to use conditional formating, what condition would cause
the color change. what color. this sniplet answers all three of your
questions but the conditonal formating seems not to fit. advise.

Private Sub Workbook_Open()
Range("A10:J15").Interior.ColorIndex = 3
End Sub

regards
FSt1

 

[JLGWhiz]

Hi Les,

1) How to execute macro when workbook is opened?

Use Workbook_Open event. In VBE project window, right click ThisWorkbook.
Example code below:

Private Sub Workbook_Open()
'Macro name goes here
End sub

2) What is the code to change the color for the cells?

First, set your range to a variable.

Set myRange = Sheets(2).Range("B2:G500")

Second, Use condition for evaluation to execute change

If myRange.FormatConditions(1).Interior.ColorIndex <> 3 Then
myRange.FormatConditions(1).Interior.ColorIndex = 3
End If

If you are not using Conditional Formatting to color the cells
initially, then omit the FormatConditions(1) part of the above
code. The cells interior can be colored on two levels and
the color change has to be on the same level.


3) Can an entire range be changed at once or do I have to change each cell
one at a time?

Yes, the entire range can be changed in one action. It is the Interior
property that keys the change.

 

[JLGWhiz]

this might be a more meaningful way to approach the change.

If myRange.FormatConditions.Count > 0 Then
myRange.FormatConditions(1).Interior.ColorIndex = 3
Else
myRange.Interior.ColorIndex = 3
End If

 

[JLGWhiz]

Out of curiosity, when you refer to "chart", do you mean table or graph?

 

[WLMPilot]

I guess chart is not the correct term to use. It is actually a spreadsheet,
not a chart derived from data. Thanks for your input. I will try them out,
but they look like what I am looking for.

Les