I have a VBA code that highlights any cells when the formula value in the cell changes in value between a range. I close the workbook. I then open the workbook but now any cell that has a value in my range will become highlighted. I don't want this to happen. I only want the cells that are highlighted prior to closing the workbook to stay highlighted.
Now, how do I keep these highlights on my worksheet?
Seeking your VBA expertise!
Re: VBA code highlights cell when formula value in the cell changes
Can you post the code that you are using? it sounds like the code you have is doing something else that you don't need it to do, but is highlighting the cells that change as a "side-effect"
Re: VBA code highlights cell when formula value in the cell changes
I have three codes in different places. See below.
Standard module
Public cVals As New Dictionary
Sub populateDict()
Dim rng As Range, c As Range
With ThisWorkbook.Worksheets("Sheet1")
Set rng = Intersect(.UsedRange, .Range("CP:CV"))
For Each c In rng
cVals(c.Address) = c.Text
Next c
.Calculate
End With
End Sub
Worksheet module
Private Sub Worksheet_Calculate()
Dim rng As Range, c As Range
Dim rngToColor As Range
On Error GoTo ErrorHandler
Application.EnableEvents = False
'get only used part of the sheet
Set rng = Intersect(Me.UsedRange, Me.Range("CP:CV"))
If rng Is Nothing Then GoTo ExitHere ' if there is no formulas in CP:CV - exit from sub
For Each c In rng
'check if previous value of this cell not equal to current value
If cVals(c.Address) <> c.Text Then
'if so (they're not equal), remember this cell
c.ClearComments
c.AddComment Text:="Changed value from '" & cVals(c.Address) & "' to '" & c.Text & "'" & " on " & Format(Date, "mm-dd-yyyy") & " by " & Environ("UserName")
c.Interior.ColorIndex = 36
End If
'store current value of cell in dictionary (with key=cell address)
cVals(c.Address) = c.Text
Next c
ExitHere:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
Resume ExitHere
End Sub
Workbook module
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Call populateDict
Application.Calculation = xlCalculationAutomatic
End SubRe: VBA code highlights cell when formula value in the cell changes
When the workbook opens, it will calculate the cells automatically - this in turn, will fire the "Worksheet_Calculate" Event which is probably mapping the whole opening event as a change. Have you tried using the "Worksheet_Change" event instead? I think this could be used more effectively for what you need and the Event wouldn't be fired when the workbook opens.
Even though you have the calculation mode defined in the Workbook_Open event, I'm pretty sure Excel does the opening calculations before it fires this event anyway. I might be wrong though.
Re: VBA code highlights cell when formula value in the cell changes
I need the worksheet calculate event because my file has vlookups to a source file. Worksheet change event will not highlight these vlookup reference cells when the formula values change; only when changes are entered manually. Right? How would I go about adding a worksheet change event to this code to prevent the code from highlighting any cell that contains a value in my range?
Re: VBA code highlights cell when formula value in the cell changes
Okay, there may be some mis-communication here: When you say "Formula Values" do you mean the actual formula changes? or the value that is produced from that formula changes?
Again, do you have an example that you can upload?
Re: VBA code highlights cell when formula value in the cell changes
The formula value I am talking about refers to the value in the cell that is produced from a formula change. I was thinking...can I just use conditional formatting to highlight cells yellow whenever the cell that is produced from a formula changes?
Re: VBA code highlights cell when formula value in the cell changes
Hi ProvenNewbie,
Since the clee values change due to formulas it can not be done with conditional formating or vba
Re: VBA code highlights cell when formula value in the cell changes
ok
Re: VBA code highlights cell when formula value in the cell changes
Hello,
its just one off those things.. threr is a difference between manually changing a cells value and formula changing a cells value..one is triggered the other just happens
Re: VBA code highlights cell when formula value in the cell changes
i'll look into this more. hopefully someone can assist.
Quote from pike;710407Hello,
its just one off those things.. threr is a difference between manually changing a cells value and formula changing a cells value..one is triggered the other just happens
Re: VBA code highlights cell when formula value in the cell changes
Hi ProvenNewbie,
No Problem, I'll close this thread and you can start a new thread with a similiar/reworked question/title.
Don’t have an account yet? Register yourself now and be a part of our community!