Conditional Formatting 4 conditions (Excel 97)

Topic

New Reply

Hellor All

I am trying to do a conditional format with 4 different conditions. Can this be done using conditional format or would it have to be vb?

I want the cells D3:AG3 to show one of 4 different colours if AK

Reply | Quote

Replies

You can add two more if you are willing to accept you can only change the colour of the font. Format the cells using this custom format string:

[ red][=1]general;[ blue][=2]general;General

then use the conditional format to colour 3 and 4

NB: remove the space before RED and BLUE (I had to add them, otherwise the Lounge showed it like this:

[=1]general;[=2]general;General

Reply | Quote

Hi Jan

Changing the colour of the font isnt really desirable. I think the attached example will show better what I am trying to describe. If it isnt possible, it is not life threatening. I can live without it.

Kerry

Reply | Quote

Yup, that needs a macro. You might use the change event in the module behind the worksheet, checking for a change in column AK.

Reply | Quote

Any chance you could tell me how to do that?

Kerry

Reply | Quote

Activate the Visual Basic Editor (Alt+F11)
Activate the Project Explorer (Ctrl+R)
Double click the sheet (Sheet1 in your example)
Enter or paste the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lngRow As Long, lngColorIndex As Long
‘ Test if cell that changed is in range AK2:AK9
If Not (Intersect(Target, Range(“AK2:AK9”)) Is Nothing) Then
‘ Get row number
lngRow = Target.Row
‘ Get color index
Select Case Target.Value
Case 1
lngColorIndex = 36 ‘ yellow
Case 2
lngColorIndex = 34 ‘ pale blue
Case 3
lngColorIndex = 40 ‘ orange
Case 4
lngColorIndex = 15 ‘ gray
Case Else
lngColorIndex = -4142 ‘ transparent
End Select
‘ Apply to columns C:AH in row
Range(“C” & lngRow & “:AH” & lngRow).Interior.ColorIndex = lngColorIndex
End If
End Sub

Reply | Quote

Thanks Hans

I tried this but I couldnt get it to work. Not sure why? Did you get it to work on the sample?

Kerry

Reply | Quote

I have attached your workbook with the Worksheet_Change event handler. Try changing one of the entries in column AK.
Note 1: This is a rough version just meant to demonstrate the idea. You should add error handling.
Note 2: I removed the external links from the workbook.
Note 3: If you are sure you’ll never need more than 4 colors, Zeddy’s method will suffice – it’s simple and it works. But it can’t be expanded to many colors, while the Woksheet_Change macro is flexible.

Reply | Quote

Hi Hans

Once again you have a very good point. At this stage there are only 4 options, but there is no reason to say that in the future there may be more. I like the flexibility of the Macro. Once again I am learning to look at different options.

I realised when I tried both solutions (yours and Zeddy’s) that I have some other conditional formatting on the cells (C to AH) such as shading weekends, which I dont want to loose. Is it possible to edit your macro to make it refer to Columns A and B and AI to AK? I was able to do this with Zeddy’s solution by just applying the conditional format to only those columns.

Your help is much appreciated Hans.

Regards

Kerry

Reply | Quote

If you want the shading to be applied to columns A, B and AI to AK, replace the instruction

Range(“C” & lngRow & “:AH” & lngRow).Interior.ColorIndex = lngColorIndex

by these two:

Range(“A” & lngRow & “:B” & lngRow).Interior.ColorIndex = lngColorIndex
Range(“AI” & lngRow & “:AK” & lngRow).Interior.ColorIndex = lngColorIndex

Reply | Quote

THANKYOU! Hans.

One more little question……….. where do you get the numbers for different colours?

Kerry

Reply | Quote

Activate the Visual Basic Editor. In the online help index, type ColorIndex. If I remember correctly, you’ll get a list of color swatches with corresponding numbers.

Otherwise, select a cell with the desired shading, then activate the Visual Basic Editor (Alt+F11), open the Immediate window (Ctrl+G), type ? ActiveCell.Interior.ColorIndex and press Enter.

Reply | Quote

How can I make this Macro apply to 12 worksheets in my workbook (one for each month) without putting the macro in for each sheet?

Kerry

Reply | Quote

Put the code in the Change Event routine for the ThisWorkbook object. If your code is like Hans’ original code, the code probably will not need to be changed.

Reply | Quote

In XL97 the only change event is in a worksheet. There is no change event for the workbook. There is a sheetchange event for the workbook, but that won’t do it. You would have to put it in each worksheet change event. Though you could write code in a “normal module” and have the individual worksheet change event call this macro, so there is only ONE source to change.

Steve

Reply | Quote

In my XL97 I can have:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
MsgBox Sh.Name & “,” & Target.Address
End Sub

Have I missed something?

Reply | Quote

Either you have or I have:

I thought we were trying to trigger a macro to change when you make a change to a worksheet (not when you change to a different worksheet). Since the poster wanted it on 12 worksheets, the poster asked if 12 routines had to be written. Legare suggested workbook change event.

My point was that XL97 has no Workbook change event. Yes, you can trigger when you change worksheets, but not when you make a change IN any worksheet which (I think) is what is needed.

Posters is conditional formatting for more than 4 colors which can NOT be done directly.

Steve

Reply | Quote

You have.

The sheetActivate and deactivate events are for what you described.

The SheetChange event does what we want: fire on a change in a cell in any sheet.

Reply | Quote

Yikes!

I think I have stirred up a hornet

Reply | Quote

The merged cells complicate things – interactively, there is no problem, but I run into problems when trying to sort in code. Therefore, I inserted empty rows in Accruals and the month sheets. I set the row height very small to minimize the visual impact.

The macro SortSheets will sort Accruals and the month sheets. You will have to run it manually in this version. I also moved the conditional formatting macro to ThisWorkbook.

Sub SortSheets()
Dim sh As Worksheet
With Worksheets(“Accruals”)
.Range(“A4”).End(xlToRight).End(xlDown).Sort _
Key1:=.Range(“A4”), Key2:=.Range(“B4”), Header:=xlNo, MatchCase:=False
End With
SortSheet Worksheets(“Apr 03”)
SortSheet Worksheets(“May 03”)
SortSheet Worksheets(“Jun 03”)
End Sub

Sub SortSheet(sh As Worksheet)
Dim rng As Range
Set rng = sh.Range(“A4”).End(xlToRight).End(xlDown)
With rng
.Sort _
Key1:=rng.Cells(1, rng.Columns.Count), Key2:=rng.Cells(1, rng.Columns.Count – 2), _
Key3:=rng.Cells(1, 1), Header:=xlYes, MatchCase:=False
End With
End Sub

See attached workbook.

Reply | Quote

Forgive me for my ignorance Hans, but where do I put in the “Sort Sheets” Macro?

I put it in the Accruals but when I run this it did not update the sectors in any of the worksheets??

Help I am confused.

Reply | Quote

If you take a look at the workbook I attached to my previous reply, you’ll see that the SortSheets macro is in a standard module (one created by Insert | Module), not in one of the class modules.

Reply | Quote

Hello Hans

I am sorry to be a pest. I have been working on your last post with changes to make the sheets sort. It works “almost” perfectly and I have tried to work around it but with no luck.

The problem is that when the Sector is put in the “Accruals’ worksheet, it does not trigger an update on the monthly worksheets. If it doesnt do that, then it defeats the purpose of automating the sort process.

Is this difficult to rectify?

Also I tried to lock and protect the cells in the monthly worksheets that link back to the accruals, but this stops the macros from working. I can live with it f it is not possible to fix, but I am afraid a user will delete the formula link inadvertantly.

Thanking you again for your help.

Kerry

Reply | Quote

I don’t understand. I thought that you wanted the user to be able to change the sector in each of the monthly worksheets. How should “putting a sector in Accruals” influence the monthly worksheets if the sector can be set independently in each of the worksheets?

If you want the sector values in the monthly worksheets to be linked to those in Accruals, you should have formulas =Accruals!E17 etc in the sector column in the monthly worksheets, and you should change the background colors in these sheets from Accruals (the SheetChange event doesn’t occur if a cell value changes because it contains a formula). This is becoming much more complicated than I first thought. I don’t think you would want to have the background colors in all these sheets updated every time you change something in Accruals – although I haven’t tested it, I fear that the event handler would become too slow. As an alternative, you can update the colors from a command button, just like the sorting (even from the same button, if you like).

You could unprotect the worksheets before updating/sorting, then protect them again.

Reply | Quote

I am sorry my explanation was not clear. Perhaps even at the time I wasnt too clear on what I was trying to do myself!!

I have attached the actual document (changed the real names) to look at.

Yes I do want to update the Monthly worksheets by making the change in the Accruals sheet. I have made a reference in the monthly sheets for this work. Now all I need is for the macro on “This Workbook” to fire when the sector number is placed in the Accruals sheet and thereby changing the sector colours in the Monthly worksheets. I dont care if it does it at the time the sector is put in or by clicking on a command button. Either way, I have no idea how to make it happen?

Kind Regards
Kerry

Reply | Quote

I’ll have a look at it later today.

Reply | Quote

Hi Kerry,

I have removed the Workbook_SheetChange event handler, since it is not useful any more. Instead, I have modified its code and put it into the standard module:

Sub ColorSheet(sh As Worksheet)
Dim lngRow As Long, lngColorIndex As Long
For lngRow = 5 To sh.Range(“AK5”).End(xlDown).Row
Select Case sh.Range(“AK” & lngRow).Value
Case 1
lngColorIndex = 40
Case 2
lngColorIndex = 35
Case 3
lngColorIndex = 37
Case 4
lngColorIndex = 36
Case 5
lngColorIndex = 15
Case Else
lngColorIndex = -4142
End Select
sh.Range(“A” & lngRow & “:B” & lngRow).Interior.ColorIndex = lngColorIndex
sh.Range(“AI” & lngRow & “:AK” & lngRow).Interior.ColorIndex = lngColorIndex
Next lngRow
End Sub

This is called after sorting a sheet. See attached zipped workbook.

Reply | Quote

Thankyou very much Hans that works wonderfully now!

I want to change the sort so that the sector is in descending order to put the “O”s to the bottom of the monthly sheets. I recorded a macro to see if I could find what needed to be changed, it was quite different to your code. (here is yours)

After this hopefully, I will leave you alone

Sub SortSheet(sh As Worksheet)
Dim rng As Range
Set rng = sh.Range(“A4”).End(xlToRight).End(xlDown)
With rng
.Sort _
Key1:=rng.Cells(1, rng.Columns.Count), Key2:=rng.Cells(1, rng.Columns.Count – 2), _
Key3:=rng.Cells(1, 1), Header:=xlYes, MatchCase:=False
End With
End Sub

Kerry

Reply | Quote

That’s not difficult: add , Order1:=xlDescending to the sort statement to change the sort order of the first sort key to descending. (I left out the sort order from my original code because the default order is ascending).

Sub SortSheet(sh As Worksheet)
Dim rng As Range
Set rng = sh.Range(“A4”).End(xlToRight).End(xlDown)
With rng
.Sort _
Key1:=rng.Cells(1, rng.Columns.Count), Order1:=xlDescending, Key2:=rng.Cells(1, rng.Columns.Count – 2), _
Key3:=rng.Cells(1, 1), Header:=xlYes, MatchCase:=False
End With
ColorSheet sh
End Sub

Reply | Quote

Yes, you (and Legareare) are both correct. I was mistaken.

I always thought thisworkbook sheetchange had the functionality of the sheetactivate event, ie a change OF sheet as opposed to the (CORRECT) change IN sheet CONTENTS.

Thank you for the clarification.

Steve

Reply | Quote

I must say, naming the event SheetChange isn’t quite clear and (proven today) may give rise to confusion. I too have confused this event with another in the past (and since I was pointed out the mistake, never forgot ).

Reply | Quote

The discussion following this question may be confusing. Legare’s suggestion to move the code to the ThisWorkbook module works – see attached workbook.

Reply | Quote

Hello Hans

Yes you are right the conversation did loose me. Thankyou for this suggestion.

This thread is getting so big, I missed your post and sent another with a zipped attachement. I am hoping you get to have a look at it for me.

Kerry

Reply | Quote

I looked at your file and have applied conditional formats to give what you want (see sheet ‘rz’ in attachment).
Note: this will only work provided your AK column contains one of 1,2,3, or 4.
You are only allowed up to 3 conditional formats so the trick is to set the default backround to one of your 4 conditions and then use the three allowed conditional formats for the other three.
In the exmple attached, I set the default background colour to your ‘1’ condition.
This background will remain unless the column AK value is either 2, 3 or 4.

zeddy

Reply | Quote

Thankyou Zeddy

This is a simple resolution to the problem and works well.

Regards Kerry

Reply | Quote