Cell fill color

[Mir Khan]

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

 

[OssieMac]

Conditional formatting is the way to go. However, you cannot use a reference
to another worksheet or workbook in conditional format but what you can do is
in some out of the way area of your worksheet you can make a cell = the cell
on the other worksheet and then you can refer to a cell on the same worksheet
for the conditional format.

Example say Sheet1!AA3 = Sheet2!A3.

Use following formula for the conditional format on Sheet1:- =AA3=3

 

[Gord Dibben]

You can do this with Conditional Formatting if you create a name for the sheet
two cell.

With Sheet two selected.

Insert>Name>Define

myname......refers to: =Sheet2!D5

Then in Sheet one CF>Formula is: =myname>123


Gord Dibben MS Excel MVP

 

[Gord Dibben]

you cannot use a reference
to another worksheet or workbook in conditional format

Not entirely true.

See my reply to OP


Gord

 

[Mir Khan]

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

 

[OssieMac]

Thanks Gord. Another thing I have learnt. My main reason for being involved
with this forum is that one learns so much from it and that's the second one
today from yourself.

 

[Gary Keramidas]

maybe you could use something like this

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

 

[Don Guillett]

Gord, etal would appreciate it when posters FULLY state their problem in the
OP.

 

[Gary Keramidas]

don:

i thought mvp's were able to discern everything that everybody was trying to
do.<g>

 

[Don Guillett]

For sometime, we have been trying, without success, to get Bill Gates to pay
for mind reading classes for MVPs.

 

[Gord Dibben]

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

 

[Mir Khan]

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