12 conditional formats

AmberJo

New Member
Joined
Apr 15, 2002
Messages
17
I know it is possible to do 3 conditional formats (or 4- using anything that doesn't fit the 3 conditions as a 4th condition)- but I need to do 12 (or 11- depending on how you look at it). Is this possible? And yes, it is necessary. I have 12 stages that a document can be at (eg first edit, first process, first review, second edit etc all the way through to online).

Any help would be greatly appreciated.

Thanks,

Amber-Jo
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi There
If your conditions are not too complex, eg color cell blue if number = 618.5 or color cell green if text = "Final Count", you might like to try the following:

Right click your sheet tab, left click View Code and paste the following code in the white area:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant

Count = 0
Do Until Count = 12
If Target.Value = Range("A1").Offset(Count, 0).Value Then
x = Range("A1").Offset(Count, 0).Interior.ColorIndex
End If
Count = Count + 1
Loop

Target.FormatConditions.Delete
Target.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=Target
Target.FormatConditions(1).Interior.ColorIndex = x

End Sub

Now use cells A1:A12 to hold your conditions, say they are the numbers 1 to 12.
Then colour each of those cells the colour you desire.
Now when you enter one of those numbers anywhere on the sheet the cell will assume the colour you have set. As the colour is in conditional formatting it will update if the value changes (eg as result of a formula).
Beware, however, that once you have set your colours in A1:A12 changing them will not automatically update your sheet.
You are not restricted to 12 colours/conditions - eg you could use A1:A55 but you would then need to change the line "Do Until Count = 12" to "Do Until Count = 55" .

Hope this is of some use to you.
regards
Derek
 

AmberJo

New Member
Joined
Apr 15, 2002
Messages
17
Dear Derek,

Would it be possible to tweak this code so that my colour legend thingy (ie cells a1:a12 that you refer to) could be on another worksheet?

Also, the column with the conditions in it is e column. How will this effect things?

And yes, I am one of those hideous, fussy users (I apologise profusely at this point).

Thanks,

Amber
This message was edited by AmberJo on 2002-11-04 17:18
This message was edited by AmberJo on 2002-11-04 17:54
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592

ADVERTISEMENT

Hi Amber

Sorry for the delay (had to go to bed).
Yes you can have the colour code on a different worksheet and you can restrict it to Column E.

As before, right click your sheet tab, left click View Code and paste this new code in the white area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
For Each cell In Target
Dim x As Byte
On Error GoTo Errorhandler
Count = 0
Do Until Count = 12
If cell.Value = Worksheets("Code").Range("A1").Offset(Count, 0).Value Then
x = Worksheets("Code").Range("A1").Offset(Count, 0).Interior.ColorIndex
cell.FormatConditions.Delete
cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=cell
cell.FormatConditions(1).Interior.ColorIndex = x
End If
Count = Count + 1
Loop
Next
Exit Sub
Errorhandler:
cell.FormatConditions.Delete
End If
End Sub


The first line "If Target.Column = 5 Then" together with the last line "End If" restricts the macro to entries you make in Column E. If you want it to work on all cells, just delete these two line (or alter to suite).

I have used a worksheet called "Code" to hold my colour codes in "A1:A12". If the worksheet you use has a different name you must replace "Code" with your worksheet's name.

This code will also let you select and enter into more than one cell in column E, using the Control+Enter method.

If it is easier for you, I can e-mail you an example spreadsheet. I also have a spreadsheet that allows you to conditionally colour up to 30 different number ranges, if that is of interest to you.

Let me know if you want me to e-mail.

regards
Derek
 

AmberJo

New Member
Joined
Apr 15, 2002
Messages
17
Dear Derek,

It just doesn't seem to be working for me. I can't even send you a sample couple of lines from the spreadsheet because its got links to a database and a word doc (well a TOC in a word doc anyway).

I've cut and pasted your text exactly as it was in your post. Any ideas (I'm still looking at a glorious white spreadsheet here). Hopefully I've just done something really silly and fixable...

Thanks,

Amber
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592

ADVERTISEMENT

Hi Amber
I am going to e-mail you two worksheets that may make it easier for you to follow.
They both contain macros (obviously) but they are safe and the files have been screened for viruses.
Let me know how you go
regards
Derek
 

BuzzG

Board Regular
Joined
Sep 8, 2002
Messages
67
Derek,
I am also interested in the multi color conditional format code you showed to Amber. I could not get the code to work. Can you Email me a sample spreadsheet also.
Many Thanks,
BuzzG
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi BuzzG
Sorry for delay - been away from office.
Have sent you 3 spreadsheets by e-mail
regards
Derek
 

Forum statistics

Threads
1,148,173
Messages
5,745,179
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top