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

  • Thread starter Thread starter Michael Lanier
  • Start date Start date
M

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
 
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
 
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
 
Thanks to you both for your help. I'll try things out over the
weekend.

Michael
 
Back
Top