Set a conditional format on value in another worksheet?
 

I need to set a conditional format based on a value that is in a worksheet
other than the one in which I'm setting the conditional format. Excel tells
me this cannot be done. Is there a workaround? Thanks!

Set a conditional format on value in another worksheet?
 

Suppose the condition relates to the value of A1 on Sheet1 and you are
working on Sheet2
Somewhere in an unused cell in Sheet2 enter =Sheet1!A1.
Let's say this was places in Z1
Now write a conditional format that references Z1
The column/row with =Sheet1!A1 could be hidden
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Wanda" wrote in message
...
I need to set a conditional format based on a value that is in a worksheet
other than the one in which I'm setting the conditional format. Excel
tells
me this cannot be done. Is there a workaround? Thanks!

Set a conditional format on value in another worksheet?
 

Not particularly neat but here's one

Copy the value in the other workbook and then paste special & paste link
into your current workbook,. It's then a simple enough matter to set the
conditional format based on the paste linked cell using VBA sheet change
event.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Worksheets("sheet1").Cells(12, 1).Value = "gggggg" Then
Worksheets("sheet1").Cells(13, 1).Interior.ColorIndex = 3
ElseIf Worksheets("sheet1").Cells(12, 1).Value = "hhhhhh" Then
Worksheets("sheet1").Cells(13, 1).Interior.ColorIndex = 5
Else
Worksheets("sheet1").Cells(13, 1).Interior.ColorIndex = xlNone
End If
End Sub

Change ggggg & hhhhh to match the values you want to capture for format and
the format is being applied to cell a13

"Wanda" wrote:

I need to set a conditional format based on a value that is in a worksheet
other than the one in which I'm setting the conditional format. Excel tells
me this cannot be done. Is there a workaround? Thanks!

Set a conditional format on value in another worksheet?
 

Wanda

If you name the cell on the other worksheet you're good to go.

On sheet2 select the cell with the value and InsertNameDefine. Give it a name
like MyName

On sheet1 your A1 CF formula will be =A1<MyName


Gord Dibben MS Excel MVP


On Thu, 18 Jan 2007 12:07:02 -0800, Wanda
wrote:

I need to set a conditional format based on a value that is in a worksheet
other than the one in which I'm setting the conditional format. Excel tells
me this cannot be done. Is there a workaround? Thanks!