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

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

--
Regards,

OssieMac


"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

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

On Sat, 29 Mar 2008 17:00:01 -0700, OssieMac
wrote:

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

Not entirely true.

See my reply to OP


Gord

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

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.

--
Regards,

OssieMac


"Gord Dibben" wrote:

On Sat, 29 Mar 2008 17:00:01 -0700, OssieMac
wrote:

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

Not entirely true.

See my reply to OP


Gord

maybe you could use something like this

http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm

--


Gary


"Mir Khan" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mir Khan" wrote in message
...
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

don:

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



--


Gary


"Don Guillett" wrote in message
...
Gord, etal would appreciate it when posters FULLY state their problem in the
OP.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mir Khan" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
don:

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



--


Gary


"Don Guillett" wrote in message
...
Gord, etal would appreciate it when posters FULLY state their problem in
the OP.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mir Khan" wrote in message
...
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

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

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