How can I conditional format cells with more than 3 conditio

gferrier · Mar 21st, 2002, 07:47 PM

How would I conditionally format a group of 5 cells on the basis that one of the 5 cells is equal to any number of 10 possible conditions.

eg. If cell 3 of cells 1-5 is = x, then cell is light green background, but if y, z, a, b, c, d, e, f... it is another colour.

Need a macro?

Any help appreciated.
garth

Tom Schreiner · Mar 21st, 2002, 09:09 PM

Hi. This is one way you can do it
Edit to suit
This code will change the interior color of the cells A1:A5 if the value entered is 1 or 2. You can add as many Case statements as you wish and format the cell any way you wish. For colors you can use ColorIndex or RGB.. If you want the format reset when a value is removed, you will have to add code for that as well... See help files. Have Fun!

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Row < 6 Then
Select Case Target.Value
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 3
End Select
End If
End Sub

gferrier · Mar 21st, 2002, 10:14 PM

Advice appreciated, though I am still a little lost.

Some more background... I am doing a map of a floor plan in which there are multiple 5rowx2col locations. I want to, on the basis of one of the cells within each of these locations, give every cell for each location a particular background color.

Unfortunately these locations are not in any uniform order because of the floor plan! Is there anyway to set up a loop that will look for each of the locns??

rgds

Tom Schreiner · Mar 22nd, 2002, 01:29 AM

I think i understand what you are saying.
When a certain value is entered into a cell then the whole group to which that cell belongs changes to a certain color???

In continuing, the groups however are different and random???

If this is the case, then it is beyond my scope.

Sorry

Good Luck

Tom Schreiner · Mar 22nd, 2002, 01:34 AM

Are these floor plans which will be reused??

Anyway, you can define ranges and then test the active cell relative to a "Group" you have predefined.

If this is your goal then please send me info on two of your groups, the color for each, the value(s) to watch for, and in which or all cells in it's particular group...

You can test for any position/selection and/or value.
So it seems to me that your floor plan is very doable.

Repost Here or send me an e-Mail

tstom@hotmail.com

Derek · Mar 22nd, 2002, 02:15 AM

Hi garth

This might give you some ideas
In each cell in area 1 type the formula = $A$1
In each cell in area 2 type the formula =$B$2
etc etc
Right click sheet tab, left click View Code and paste in this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then
On Error Resume Next
For Each cell In Range("A1:T50")
cell.Interior.ColorIndex = cell.Value
If cell.Row <> 1 Then cell.Font.ColorIndex = cell.Value
Next
End If
End Sub

Change A1:T50 to the area that covers your floor plan

Now when you enter a number in A1 that cell and all cells in area 1 will change
Now when you enter a number in B1 that cell and all cells in area 2 will change
The line….If Target Row = 1…..ensures this macro only runs if you make an entry in row 1
The line …if cell.Row <> 1….ensures you can still read the number you type in row 1
to enable you to see what number relates to which color

Good luck
regards
Derek

[ This Message was edited by: Derek on 2002-03-22 01:17 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 07:47 PM	#1
gferrier New Member Join Date: Mar 2002 Posts: 2	How would I conditionally format a group of 5 cells on the basis that one of the 5 cells is equal to any number of 10 possible conditions. eg. If cell 3 of cells 1-5 is = x, then cell is light green background, but if y, z, a, b, c, d, e, f... it is another colour. Need a macro? Any help appreciated. garth

Mar 21st, 2002, 09:09 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi. This is one way you can do it Edit to suit This code will change the interior color of the cells A1:A5 if the value entered is 1 or 2. You can add as many Case statements as you wish and format the cell any way you wish. For colors you can use ColorIndex or RGB.. If you want the format reset when a value is removed, you will have to add code for that as well... See help files. Have Fun! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Row < 6 Then Select Case Target.Value Case 1 Target.Interior.ColorIndex = 3 Case 2 Target.Interior.ColorIndex = 3 End Select End If End Sub

Mar 21st, 2002, 10:14 PM	#3
gferrier New Member Join Date: Mar 2002 Posts: 2	Advice appreciated, though I am still a little lost. Some more background... I am doing a map of a floor plan in which there are multiple 5rowx2col locations. I want to, on the basis of one of the cells within each of these locations, give every cell for each location a particular background color. Unfortunately these locations are not in any uniform order because of the floor plan! Is there anyway to set up a loop that will look for each of the locns?? rgds

Mar 22nd, 2002, 01:29 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I think i understand what you are saying. When a certain value is entered into a cell then the whole group to which that cell belongs changes to a certain color??? In continuing, the groups however are different and random??? If this is the case, then it is beyond my scope. Sorry Good Luck

Mar 22nd, 2002, 01:34 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Are these floor plans which will be reused?? Anyway, you can define ranges and then test the active cell relative to a "Group" you have predefined. If this is your goal then please send me info on two of your groups, the color for each, the value(s) to watch for, and in which or all cells in it's particular group... You can test for any position/selection and/or value. So it seems to me that your floor plan is very doable. Repost Here or send me an e-Mail tstom@hotmail.com

Mar 22nd, 2002, 02:15 AM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi garth This might give you some ideas In each cell in area 1 type the formula = $A$1 In each cell in area 2 type the formula =$B$2 etc etc Right click sheet tab, left click View Code and paste in this code Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 1 Then On Error Resume Next For Each cell In Range("A1:T50") cell.Interior.ColorIndex = cell.Value If cell.Row <> 1 Then cell.Font.ColorIndex = cell.Value Next End If End Sub Change A1:T50 to the area that covers your floor plan Now when you enter a number in A1 that cell and all cells in area 1 will change Now when you enter a number in B1 that cell and all cells in area 2 will change The line….If Target Row = 1…..ensures this macro only runs if you make an entry in row 1 The line …if cell.Row <> 1….ensures you can still read the number you type in row 1 to enable you to see what number relates to which color Good luck regards Derek [ This Message was edited by: Derek on 2002-03-22 01:17 ]