All cell contents within a range of cells turns white if Sheet1!A1 =1
 

If the return of Sheet1!A1 = 1, I need all fill, font and line colors
to turn white from A1:E10. If the value of Sheet1!A1 changes to 0,
all colors must return to normal. Does anyone have any thoughts on
this? Thanks for any suggestions.

Michael

All cell contents within a range of cells turns white if Sheet1!A1 = 1
 

If your range to format is on Sheet1.............

Conditional Formatting

Select A1:E10

FormatCFFormula is: =$A$1=1 Format to suit

If range to format is on a sheet other than Sheet1

Select Sheet1!A1 and give it a name..............insertnamedefine

On other sheet select range A1:E10 and FormatCF

Formula is =myname=1 where myname is your defined name for Sheet1!A1


Gord Dibben MS Excel MVP

On Fri, 4 Dec 2009 16:22:04 -0800 (PST), Michael Lanier
wrote:

If the return of Sheet1!A1 = 1, I need all fill, font and line colors
to turn white from A1:E10. If the value of Sheet1!A1 changes to 0,
all colors must return to normal. Does anyone have any thoughts on
this? Thanks for any suggestions.

Michael

All cell contents within a range of cells turns white if Sheet1!A1 = 1
 

Copy this code to your worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:A10")
If Not Intersect(Target,rng) Is Nothing Then
If Sheets("Sheet1").Range("A1") = 1 Then
With rng
.Interior.ColorIndex = 2
.Font.ColorIndex = 2
.Borders.ColorIndex = 2
End With
End If
If Sheets("Sheet1").Range("A1") = 0 Then
With rng
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
.Borders.ColorIndex = xlAutomatic
End With
End If
End If
End Sub



"Michael Lanier" wrote in message
...
If the return of Sheet1!A1 = 1, I need all fill, font and line colors
to turn white from A1:E10. If the value of Sheet1!A1 changes to 0,
all colors must return to normal. Does anyone have any thoughts on
this? Thanks for any suggestions.

Michael

All cell contents within a range of cells turns white ifSheet1!A1 = 1
 

Thanks to you both for your help. I'll try things out over the
weekend.

Michael