Excel Conditional Formatting with More Than Three Conditions 28

[DeLaMartre]

I have a spreadsheet in which I want Excel to apply conditional formatting to cells with seven different conditions, (different colors for each of seven numbers, "1"=blue, "2"=red, "3" =yellow, "4"=green, etc). Unfortunately, Excel limits me to three. I would be most grateful for any help.

 

[xlbo]

Not sure exactly what the problem is - do you want the formatting to apply to the "same cell" in a different worksheet ???
If so, the code will need to be in both worksheet modules

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[joem28a]

Hi there Geoff,
I didn't think I explained myself properly. Here is what I am doing, I have 1 spreadhsheet with 4 worksheets called Team 1, Team 2, Team 3 & Team 4; the 4th worksheet is the overall or statistics worksheet. Annual Leave, Maternity Leave, Sick Leave and so on is recorded on the Team worksheets. Each time a Team Leader enters data on one of the Team worksheets, it is duplicated on the stats worksheet; the way I do this is by entering "=Team 1:A1" and so on; but this does not duplicate the cell format, such as colour, etc. Because of the Conditional Formatting limitation, I cannot duplicate all of the Leave colour types. The duplication of cell data from the Team worksheets works great; just the formatting doesn't. This is where your VB code comes into effect; it only seems to work when you "directly" enter data into the cells; the code doesn't seem to work with the "=Team 1:A1" formula. The only way I make it work is to double click on the actual duplicated cells. Is there a way to manually execute the VB code?. Thank you.

Regards,
Joe.

 

[xlbo]

Try this - you need the code to manually update Sheet4:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "ARL"
oCell.Interior.Colorindex = 3
sheets("Sheet4&quot.range(oCell.address).Interior.Colorindex = 3
Case Is = "P-ARL"
oCell.Interior.Colorindex = 4
sheets("Sheet4&quot.range(oCell.address).Interior.Colorindex = 4
Case Is = "S/L"
oCell.Interior.Colorindex = 5
sheets("Sheet4&quot.range(oCell.address).Interior.Colorindex = 5
Case Is = "Maternity"
oCell.Interior.Colorindex = 7
sheets("Sheet4&quot.range(oCell.address).Interior.Colorindex = 7
Case Else
oCell.Interior.ColorIndex = xlNone
sheets("Sheet4&quot.range(oCell.address).Interior.Colorindex = xlnone
End Select
Next oCell
End Sub


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[joem28a]

Hi there Geoff,
I tried the new code; no success. The cells in Sheet 4 are just not updating !!!; the text is visible but not the background colour????. Would it be possible to send the spreadsheet to you?; I will remember you in my Will if you could solve this !!!!. Many thanks.

Regards,

Joe.

 

[xlbo]

is the sheet actually called "sheet4" ??
'cos I've just tested it and it works - if ARL is entered into Cell A1 in sheet1, that cell turns red, as does cell A1 in sheet4

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[joem28a]

Geoff,
No, the worksheet is actually called "SIG". Also, for clarification, do I place your code in the other worksheets of just the master or destination worksheet?. Thanks.

Regards,

Joe.

 

[xlbo]

The code goes in the Master spreadsheet CHANGE event. Whereever it says "Sheet4" in the last code I posted, change it to "SIG"

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[sdrp]

Judt trying to use the above codes to allow me display different colour cells when the values are in specific ranges.

i.e. < 5, between 5 and 10, > 10

can anyone help?

cheers

 

[xlbo]

Select case oCell.value

Case Is < 5
'stuff here
Case 5 To 10
'stuff here
Case Is > 10
'stuff here
Case Else
'error trap here
End Select

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sdrp]

thx for that. is there a way of running this automatically when a cell is populated with a number?

 

[KenWright]

Poor old Geoff - he keeps making that fatal mistake of assuming that people will actually read the rest of the thread before posting a question

If you take a look at Geoff's earlier postings and marry up what he has given you with the previous code, then you will find it does exactly that, eg:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case 1 To 3
oCell.Interior.ColorIndex = 3
Case Is = 4
oCell.Interior.ColorIndex = 4
Case 5 To 9
oCell.Interior.ColorIndex = 5
Case Is = 10
oCell.Interior.ColorIndex = 7
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

He also doesn't appear to have had many of the people he has helped with this post, acknowledge that help by means of awarding him a star, especially given all the clarifications he has responded to, so Star from me.

Regards
Ken...................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

And I should probably have explained that. The first line of Geoff's sub is as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

The Private bit means that it is not a macro that will appear in the list when somebody does Tools / Macros / Macro. The Worksheet_Change bit ties it to any practically any change in the current worksheet only. the ByVal Target as Range bit means that the range it will work with is whatever cell has been changed. This is all automatic, and kicks in anytime you change a value in a cell on that sheet (or in a specific range if you have limited it to a range - See earlier in the thread).

If this now fixes all your problems, then you may want to consider acknowledging Geoff's post as having been helpful, and simply click on the link at the bottom left of his post (Not mine please - It's Geoff's answer, thanks)

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

LOL - thanks for jumping in Ken. I'm not too fussed about stars any more but you are a gentleman and a scholar.



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

My pleasure

Regards
Ken............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[hugobossv8]

Great stuff! Now, how do I change the color of a cell right above the target cell (the cell right above the cell that a value has just been inputted into).

Here's what I want to do, I just don't know how to code it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = &quot;T&quot;
oCell.Interior.Color = RGB(255, 100, 0)
Cell above oCell.Interior.Color = RGB(255,100, 0)
Case Is = &quot;A&quot;, &quot;AE&quot;
oCell.Interior.Color = RGB(0, 255, 0)
End Select
Next oCell
End Sub

 

[KenWright]

use Offset

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = &quot;T&quot;
oCell.Offset(-1, 0).Interior.Color = RGB(255, 100, 0)
Case Is = &quot;A&quot;, &quot;AE&quot;
oCell.Interior.Color = RGB(0, 255, 0)
End Select
Next oCell
End Sub


Regards
Ken..............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[hugobossv8]

Thanks KenWright!

Exactly what I was looking for. I made sure to give you a star too

 

[KenWright]

My pleasure. Note that if you want it to happen for every case then you need to change the other case statement as well. I only did the one to give you the gist of what the syntax looked like.

Regards
Ken.............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[hugobossv8]

Thanks again!

Yes, I did notice that.

 

[hugobossv8]

Ok, one last question. How do I get the code below to work for a specific range - ie (&quot;A1:X50&quot - instead of the entire worksheet. I've tried a few things and couldn't get it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = &quot;T&quot;
oCell.Offset(-1, 0).Interior.Color = RGB(255, 100, 0)
Case Is = &quot;A&quot;, &quot;AE&quot;
oCell.Interior.Color = RGB(0, 255, 0)
End Select
Next oCell
End Sub