Conditional Formatting in excel 2003

October 5, 2012 at 04:17:06
Specs: Windows XP
Hi,

I need to write a macro that will fill in a row depending on one cell's value
ie if Saturday or Sunday Highlight row blue
I also need to highlight cells if they return certain values
S - red
H, AM, PM - green
At the moment I can do either or but can not work out how to get both options

Thanks in advance


See More: Conditional Formatting in excel 2003

Report •


#1
October 5, 2012 at 10:58:48
The main issue here is that you need to decide the preference.

In Excel 2003, the program checks the conditional format list and if one matches, it gets applied and processing stops.

So as you are adding the rules, you need to determine what order you want them to be checked in and which formatting you care more about.

The formatting in checking for the values of the cells themselves it straightforward, but the Saturday/Sunday one will need to either be placed at the beginning of the list if you want the Sat/Sun formatting to take precedence over the S/H/AM/PM formatting or at the end of the list if you want the S/H/AM/PM formatting to take precedence.

Obviously this would only matter for cells that match both the Sat/Sun requirements as well as the S/H/AM/PM requirements.

Let me know if this makes sense or if you need more help.

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::


Report •

#2
October 5, 2012 at 13:59:12
SongCloud is correct in his description of how Conditional Formatting works but since you mentioned using a macro, your options might not be so limited.

However, you would need to be a bit more specific if you want help with some VBA code.

What cells are you using to determine the formatting, e.g. a specific column for Saturday/Sunday, a different one for AM, PM, etc?

Are you asking for the entire row to be blue if Saturday/Sunday is found but then also for different fill colors for different cells in the same row for AM, PM, etc?

Give us some detail, or post an example of your data, and we'll see what we can do.

Before posting any example data, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
October 7, 2012 at 04:16:46
I need to combine:-

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("C3:M368")
For Each Cell In MyPlage

If Cell.Value = "S" Then
Cell.Interior.ColorIndex = 38
End If
If Cell.Value = "H" Then
Cell.Interior.ColorIndex = 35
End If
If Cell.Value = "PM" Then
Cell.Interior.ColorIndex = 35

End If
If Cell.Value = "AM" Then
Cell.Interior.ColorIndex = 35
End If

If Cell.Value <> "H" And Cell.Value <> "S" And Cell.Value <> "AM" And Cell.Value <> "PM" Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

and

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$B3=""Saturday"""
Selection.FormatConditions(1).Interior.ColorIndex = 41
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$B3=""Sunday"""
Selection.FormatConditions(2).Interior.ColorIndex = 41


Report •

Related Solutions

#4
October 7, 2012 at 08:34:49
First, a posting tip:

Before posting any more VBA code in this forum, please click on the blue line at the end of this post and read the instructions on how to post code and data in this forum. Thanks.

Second, since we don't know how you are using your spreadsheet, it's hard to offer advice on "combining" your two sections of code.

For example, your Worksheet_Change code looks at each cell in the Range("C3:M368") every time you make any change to the sheet. Each time it checks a cell, it essentially checks it for 8 different conditions. That's 8 x 4392 (35,136) "decisions" that VBA has to make every time any change is made to the sheet. For some reason that seems extremely inefficient to me.

However, since I don't know where the changes are being made, perhaps that is the only way to do it. However, if the change is being made within that range, and the only cell that needs to be checked is the one that changed, then the number of decisions can be reduced to 8 or less.

If you'll explain what changes are being made that triggers the code, perhaps we can offer some suggestions as to how to make the code more efficient.

Third, I don't know what you are trying to do with this section of code:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$B3=""Saturday"""
Selection.FormatConditions(1).Interior.ColorIndex = 41
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$B3=""Sunday"""
Selection.FormatConditions(2).Interior.ColorIndex = 41

Please explain what you are trying to do with that and why you want it "combined" with the Worksheet_Change code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
October 7, 2012 at 09:12:56
I am creating a holiday chart for my team. I have created seperate tabs for each person but I am then compiling this into a summary sheet.
Column A has the dates and Column B has the day. As we don't work weekends I want to highlight all the weekends, so if I had a conditional format on column B I can highlight all cells in that row blue.
I then have the cells in columns C-M populated with H - holiday, AM/PM half day holiday or S sick. I also want to highlight these cells so a clear colour coding can be seen at a glance.
The first marco colour codes the cells according to the second part of my query, however I can not add in the section to colour code the entire row

Report •

#6
October 7, 2012 at 13:01:07
If you want, you do not need to use a Macro for your Conditional Formatting.

See if this works for you:

This is for Excel 2007

There are Three separate Rules,
and they must be in the correct order,
so after your done entering all the formulas
they should be, in the following color order:

Green on Top
Red in the Middle
Blue on Bottom

First Formula:

1) Select your cell or Range of Cells, B3:M368
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =OR($B3="Saturday",$B3="Sunday")
Note: The Dollar Signs before the Column Letter must be used.

6) Click on the Format button
7) Select the Fill Tab
8) Select Blue color
9) Click OK
10) Click OK

Second formula:

1) Select your cell or Range of Cells, should be the same as above.
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =C3="S"

6) Click on the Format button
7) Select the Fill Tab
8) Select Red color
9) Click OK
10) Click OK

Third Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =OR(C3="AM",C3="PM",C3="H")

6) Click on the Format button
7) Select the Fill Tab
8) Select Green color
9) Click OK
10) Click OK

Note: Only the first formula, for Blue uses the $ sign.

MIKE

http://www.skeptic.com/


Report •

#7
October 7, 2012 at 13:07:56
I still don't understand why you are checking every cell in your range every time you make a change to the sheet. Why aren't you just setting the color of the specific cell in which the change is made?

e.g. if you enter an H in D10, let the Worksheet_Change macro set the color for D10. That's normally what a Worksheet_Change macro is used for. It doesn't make sense to check the contents of the other 4391 cells every time you make a change any where in the sheet. If you don't know how to do that, just ask and I'll offer a suggestion.

As far as filling the weekend rows with blue, why not do that once with a macro that checks column B? Once again, I don't see the need to set the color of those rows with a Worksheet_Change macro since (I assume) that any cell that contains a weekend day won't change.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 7, 2012 at 13:38:15
Hi,

I would gladly not use Worksheet_Change but I have limited knowledge of VBA please advise a better solution


Report •

#9
October 7, 2012 at 14:00:01
There nothing wrong with using Worksheet_Change macro for what you are trying to do...it's the way that you are using it that can be improved upon.

In any case, let us know if Mike's Conditional Formatting suggestion works for you. If not, I'll offer some code that might help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question