Macro needed to fill cells based on another cell value in a different sheet

[ShuStar]

Hi guys, if anyone can help me get the macro for this to work, it would be amazing!

My VBA is lacking a little and am losing my head over this - any help would be greatly appreciated:


'if any cell value in range L3 to P73 is = 2 in the OLD v New Check Sheet
'then find corresponding cell in New sheet and highlight that cell Green in New sheet

'ie if L10 in OLD V New Check sheet is = 2, then find L10 in New Sheet and fill it Green
and if M25 in OLD V New Check sheet is = 2, then find M25 in New Sheet and fill it Green
and if O67 in OLD V New Check sheet is = 2, then find O67 in New Sheet and fill it Green...so on....

..Macro to do this check and highlight cells all the way through to the end of my range.




Thanks in advance,

ShuStar

 

[mumps]

Try:

Sub colorCell()
    Application.ScreenUpdating = False
    Dim rng As Range, fRng As Range, sAddr As String
    Set fRng = Sheets("OLD v New Check").Range("L3:P73").Find("2", LookIn:=xlValues, lookat:=xlWhole)
    If Not fRng Is Nothing Then
        sAddr = fRng.Address
        Do
            Sheets("New Sheet").Range(fRng.Address).Interior.ColorIndex = 4
            Set fRng = Sheets("OLD v New Check").Range("L3:P73").FindNext(fRng)
        Loop While fRng.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
End Sub

 

[ShuStar]

Amazing! That's done it! Thank you very much, greatly appreciated!



ShuStar

 

[ShuStar]

If you're able to and have time, it would be great if you could explain what the macro is doing line by line and why certain variables were declared as well as what some of the code is doing (ie Lookin:=xlValues, lookat:=xWhole)

Not in too much detail, but just a explanation - btw you don't have to, you've given lots of help as it is.

ShuStar

 

[mumps]

You are very welcome. Here is the macro with explanatory comments. I hoe this helps.

Sub colorCell()
    Application.ScreenUpdating = False 'turns off screen refreshing to eliminate screen flicker and speed up the macro
    Dim rng As Range, fRng As Range, sAddr As String 'it is always best to declare the variables used
    Set fRng = Sheets("OLD v New Check").Range("L3:P73").Find("2", LookIn:=xlValues, lookat:=xlWhole) 'looks for the first occurrence of "2" in the range
    If Not fRng Is Nothing Then 'if it is found then next line is executed
        sAddr = fRng.Address 'sets the addres of the first occurrence of "2" to the variable sAddr
        Do 'starts a loop
            Sheets("New Sheet").Range(fRng.Address).Interior.ColorIndex = 4 'since the value was found, it colors the same cell in the other sheet based on its address
            Set fRng = Sheets("OLD v New Check").Range("L3:P73").FindNext(fRng) 'finds the next occurrence
        Loop While fRng.Address <> sAddr 'keeps looping until it gets back to the address of the first occurrence and then stops the loop
        sAddr = ""
    End If
    Application.ScreenUpdating = True 'turns screen refreshing back on
End Sub

 

[ShuStar]

You've explained this really well - thanks so much again!

 

[mumps]

My pleasure.