Changing cell format based on value in range on other sheet

[Graham Hopkins]

I have an on button click event in one sheet, that looks at a range in
another sheet, for a particular value (in this case "R").
If that value is found anywhere in that range, it changes the format
of the specified cell "O4" on the current sheet.
I have tried the following code, but this doesn't work - any tips?

For Each c In Range("Minus_1_Customer_DECS")
If c.Value = "R" Then
Range("O4").Interior.Color = RGB(255, 0, 0)
Next c
End If

 

[David McRitchie]

You should have gotten an error dialog
Compile error:
Next without For
[OK] [HELP]

and if you did you should have reported that,
but I think the message is obvious enough that
you probably did not get the message for some reason.
Tested in Excel 2000 from within a
Worksheet_BeforeDoubleClick event macro.

place the end if before the next c

Dim c As Range
For Each c In Range("Minus_1_Customer_DECS")
If UCase(c.Value) = "R" Then
Range("O4").Interior.Color = RGB(255, 0, 0)
End If
Next c

 

[Graham Hopkins]

Thank you very much for that suggestion. That works great. However,
the cell formatting does not return to it's default when the value "R"
is no longer appearing in the range. Is there a simple way to reset
the cell format to default if that value is no longer found in the
range?
I've tried:

Else
ActiveSheet.Range("O4").Interior.Color = RGB(255, 255, 255)

but this makes the initial formatting not work.

My first choice for this whole issue was to use conditional formatting
- but I had difficulty in getting this to work - the fact that a named
range in another sheet is referred to may have been causing problems.

I was using:
Formula is:
=FIND("named_range","R")<>0

Many thanks