refresh button

[ttowncorp]

I'm using the code at the bottom to highlight specific numbers in color. problem is some times when I add new data the code does not auto color the new data unless I go to "Developer, View Code," and hit the "RunSub/UserForm" play button. is there a way I can have that button on my excel sheet in a cell as well so I don't have to keep navigating to the developer tab, or is there a way in the coding you can to auto update itself when I add new data to it?


Function Highlighter()
Dim k As Long
Dim lrow As Long
lrow = Sheet4.Range("V" & Rows.Count).End(xlUp).Row
lrow1 = Sheet7.Range("d" & Rows.Count).End(xlUp).Row
For d = 2 To lrow
For k = 2 To lrow1
If Sheet7.Range("d" & k).Value = Sheet4.Range("v" & d).Value Then
Sheet7.Range("d" & k).Interior.ColorIndex = 44
End If
Next
Next

For k = 2 To lrow
For d = 2 To lrow1
If Sheet4.Range("v" & k).Value = Sheet7.Range("d" & d).Value Then
Sheet4.Range("v" & k).Interior.ColorIndex = 44
End If
Next
Next

End Function

 

[Scott T]

You may want to make the UDF volatile. See link below.

https://www.mrexcel.com/forum/excel-questions/271165-udf-volatile-vs-not.html

 

[ttowncorp]

thank you but that got way over my head as in getting it to work. that sounded way more complicated than what I need. what about is there a way to loop it. have it always running so when I add data it will pick it up automatically?

 

[Scott T]

To make it volatile you would add this line of code to the UDF

Application.Volatile

You could also hit F9 to force the entire sheet to recalculate.

 

[ttowncorp]

I will try that thank you