Cell fill color
Hi Gord, I have tried to detail my question.
I have total 3 sheets in this workbook and I need a macro that will involve
2 out of 3 worksheets and they are €śFloorPlan post restack€ť and €śData Sheet€ť.
€śFloorPlan post restack€ť worksheet has FloorPlan that has seating
arrangement of the floor with cube # and other information. €śFloorPlan post
restack€ť will be pulling information from €śData Sheet€ť. €śFloorPlan post
restack€ť is color coded based on managers, whenever there is a change in
manager we make the change in "Data Sheet" and manually change color of the
cell, which should be handled by Excel.
What I want is when we change manager in €śData Sheet€ť the designated color
for the manager should change by Excel in €śFloorPlan post restack€ť. I tried
conditional formatting and it work great for 3 managers (conditions) but I
have 5 managers altogether and we might have more managers in future and
hence I am looking some code that will serve my purpose.
Please let me know if you need any additional information.
Please provide me with your email address where i can email you the Excel
file if you would like to take a look at it.
I appreciate your help and time.
Mir Khan
"Gord Dibben" wrote:
One cell.........multiple conditions?
Don't use CF, try event code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
With Me.Range("A1")
If IsNumeric(.Value) Then
Select Case .Value
Case Is <= 0: Num = 4 'bright green
Case 0 To 5: Num = 6 'yellow
Case 6 To 10: Num = 5 'blue
Case 11 To 15: Num = 7 'magenta
Case 16 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
End If
Sheets("Sheet1").Range("B1").Interior.ColorIndex = Num
End With
End Sub
This code will be placed in Sheet2 and will color A1 of Sheet1
Right-click on the Sheet2 tab and "View Code". Copy/paste into that sheet
module. Adjust cell refs and Case limits to suit.
Gord
On Sat, 29 Mar 2008 17:36:00 -0700, Mir Khan
wrote:
Thanks for the quick response...
Hi Gordon conditional formatting worked good for me but it has limit of 3
conditions and i have more than 3 conditions to use. Is there any other
alternative.
I appreciate your help.
Thanks
Mir Khan
"Gord Dibben" wrote:
You can do this with Conditional Formatting if you create a name for the sheet
two cell.
With Sheet two selected.
InsertNameDefine
myname......refers to: =Sheet2!D5
Then in Sheet one CFFormula is: =myname123
Gord Dibben MS Excel MVP
On Sat, 29 Mar 2008 16:04:00 -0700, Mir Khan
wrote:
Hi Experts need your help...
I need to change the cell fill color (in a cell in sheet one) based on the
cell value (in a cell in sheet two).
Thanks
Mir Khan
|