Posts by ProvenNewbie

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;710407

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: how do I delete my post? I mistakenly created a post

no problem here. just trying to be more specific with my questions cause I can't edit my initial post. is there an option to do that? thanks royuk

Re: how do I delete my post? I mistakenly created a post

royUK, you can delete all my post

Re: Is there a way to prevent users from deleting and rows from Excel?

thanks everyone

Re: how do I delete my post? I mistakenly created a post

Quote from royUK;710290

All your posts have replies from other members, som e of which you haven't even responded to.

royUK, you can delete this post

Re: VBA code highlights cell when formula value in the cell changes

ok

Re: how do I delete my post? I mistakenly created a post

1

how do I delete my post? I mistakenly created a post

Re: Is there a way to prevent users from deleting and rows from Excel?

In Excel, can I allow users to only edit ranges during a specific time period?

I want to prevent users from deleting rows and columns in their files because I have lookups linked to cells in their files.

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

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

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 Sub

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: if statement after countif

It works for me.

Seeking expertise on how to insert comment in a reference cell each time it changes in value in workbook 1. The reference cell contains a vlookup formula that links to workbook 2. When the lookup cell in the workbook 2 changes, the formula value in workbook will also change. I would like to insert a comment in the reference cell to say "Previous value was "previous value that was in the cell" prior to the change. I tried track changes but it doesn't work because my workbook is shared.

Re: Microsoft Scripting Runtime

Thanks everyone!!

A board member has "Microsoft Scripting Runtime" in a VBA code. What is Microsoft Scripting Runtime? Is it safe to use the VBA code with Microsoft Scripting Runtime in my excel workbooks?

Hello VBA experts.

I am looking for some VBA expertise.

I have external reference cells (vlookups) in the destination file that links to the source file. I am looking for a code where anytime a cell value changes in the source file, the external reference cells in the destination file will get highlighted yellow and stay highlighted yellow. If no cell value changes in the source file, the code will skip and move on to the next cell. Any expertise is greatly appreciated. I was thinking worksheet_calculate or worksheet_change event would work but I don't know what code to use to lookup cells in the source file.

Illustration below: The first table is the destination file. The second table below is the source file.
[TABLE="width: 440"]

ID

LookupValue1

LookupValue2

LookupValue3

LookupValue4

[TD="align: right"]8020456
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]1000
[/TD]

[TD="align: right"]8020457
[/TD]
[TD="align: right"]999
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]2000
[/TD]

[TD="align: right"]8020458
[/TD]
[TD="align: right"]70
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]6000
[/TD]

[TD="align: right"]8020459
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]80
[/TD]
[TD="align: right"]7000
[/TD]

[TD="align: right"]8020460
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]90
[/TD]
[TD="align: right"]9999
[/TD]

[/TABLE]
[TABLE="width: 440"]

ID

CellChange

CellChange

CellChange

CellChange

[TD="align: right"]8020456
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]1000
[/TD]

[TD="align: right"]8020457
[/TD]
[TD="align: right"]999
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]2000
[/TD]

[TD="align: right"]8020458
[/TD]
[TD="align: right"]70
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]6000
[/TD]

[TD="align: right"]8020459
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]80
[/TD]
[TD="align: right"]7000
[/TD]

[TD="align: right"]8020460
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]90
[/TD]
[TD="align: right"]9999
[/TD]

[/TABLE]

Here is the code that I created to start conversations and hopefully solve this post.

Private Sub Worksheet_Calculate()
dim prevvalu as integer


application.calculation = x1caculationmanual
if target.column >= 10 and target.column <=13 then
if target("a1").value <> prevvalu then
range.interior.color index = 36


end if


end if

end Sub