Cell color changes whenever the cell value changes

[Sushil]

Hi,

i have a spreadsheet where i have to update the values frequently. i want the cell color to change alternatively between 2 colors so that i know which cells have been updated. can you please help on this.


Thanks

Sushil

 

[Luke M]

Right click on sheet tab, view code, paste this in. Modify range callout as needed

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

'Which cells do you want to change colors?
Set MyRange = Range("A2:A10")
If Intersect(MyRange, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = 4 'green
Else
.ColorIndex = 3 'Red
End If
End With
Application.EnableEvents = True

End Sub

[/pre]

 

[Sushil]

works like a charm!Thanks Luke!

 

[Sushil]

one more favour... is it possible to turn it off without deleting the code and then turn it back again when required.


Thanks

 

[Luke M]

Sure thing. Just need to define a cell somewhere as the "switch".

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim SwitchCell As Boolean

'Which cell contains True/False?
SwitchCell = Range("A1").Value

'If turned off, don't run
If Not (SwitchCell) Then Exit Sub

'Which cells do you want to change colors?
Set MyRange = Range("A2:A10")
If Intersect(MyRange, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = 4 'green
Else
.ColorIndex = 3 'Red
End If
End With
Application.EnableEvents = True

End Sub

[/pre]

 

[Sushil]

Thanks! does that mean Switch cell(A1) can have only have "true" or "false" value . Also in line "Set MyRange = Range("A2:A10")" Can i have 2 or more ranges e.g A2:A10 , B5:B20,C20:C50 etc.


I really appreciate your help

 

[Luke M]

A1 can be any value technically. Unless it has a value of "FALSE" or 0, the macro will run.

Yes, you can list multiple ranges just like this:

[pre]

Set MyRange = Range("A2:A10 , B5:B20,C20:C50")

[/pre]

 

[Sushil]

Thank you Sir!

 

[Luke M]

Glad I could help. =)

 

[Sushil]

i have noticed that if the cells are linked or have any frmula in them , their color does not change unless the cell is manually changed. is it possible to change the color of these cells too. Thanks for your help.

 

[Luke M]

Not using the change event...this will run slightly slower, but you could use this macro instead:

[pre]

Private Sub Worksheet_Calculate()
Dim MyRange As Range
Dim SwitchCell As Boolean

'Which cell contains True/False?
SwitchCell = Range("A1").Value

'If turned off, don't run
If Not (SwitchCell) Then Exit Sub

'Which cells do you want to change colors?
Set MyRange = Range("A2:A10")

Application.EnableEvents = False
'Will now look through every cell in the range
For Each c In MyRange
With c.Interior
If .ColorIndex = 3 Then
.ColorIndex = 4 'green
Else
.ColorIndex = 3 'Red
End If
End With
Next c
Application.EnableEvents = True

End Sub

[/pre]

 

[Sushil]

Hi Luke,

i copy pasted the code but for some reason there is no change in color at all .


Thanks

Sushil

 

[Luke M]

Strange. You have both macros in the Sheet module?

 

[Sushil]

No. only the last one you had posted.


Thanks

Sushil

 

[Luke M]

You might need to have both. The Change macro will only affect pickup the cells with static values, and the calculate macro is needed for the cells with formulas.

 

[Sushil]

I tried with copy and pasting both the codes. color changes but color of all adjoining cells to the cell having having formula also changes e.g if the range is A2:A10 and cell A3 has value A4 has value and A5 has formula involving A3 and A4. color of A2and A5:A10 changes too. if i expand the range to say 10000 cells then excel crashes.


Thanks

Sushil

 

[Luke M]

Having that large an array of cells to monitor will definitely slow things down using the Calculate macro.


Might I propose that the data be re-arranged so that inputs are in one spot and formulas in another? Then, only monitor the changes to inputs. As formulas are simply dependents on the inputs, if one knows the inputs have changed, would it be safe to assume that the user should know that the outputs have also changed? (in summary, delete the Calculate macro, only use the change macro)

 

[Sushil]

ok Thanks