How can I conditional format cells with more than 3 conditio

gferrier

New Member
Joined
Mar 20, 2002
Messages
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top