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!

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!

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!

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!