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

[Michael Lanier]

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

 

[Gord Dibben]

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

Conditional Formatting

Select A1:E10

Format>CF>Formula 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..............insert>name>define

On other sheet select range A1:E10 and Format>CF

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


Gord Dibben MS Excel MVP

 

[JLGWhiz]

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]

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

Michael