More Help Please VoG

  steviegee 15:08 08 Sep 2009
Locked

With regards to this thread click here could you add in to the macro that once it has changed the cell in sheet 2 it colours it in red. Thanks.

  VoG II 16:27 08 Sep 2009

Try this - it may be slower if there is a lot of data:


Sub test()
Dim LR As Long, i As Long
Dim c As Range
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
For Each c In Sheets("Sheet2").UsedRange
If c.Value = .Range("A" & i).Value Then
c.Value = .Range("B" & i).Value
c.Interior.ColorIndex = 3
End If
Next c
Next i
End With
End Sub

  steviegee 11:28 09 Sep 2009

Thanks VoG but it is quite a big spreadsheet and the PC 'stops responding' when running the macro. How about changing the colour of the text instead?

  VoG II 11:46 09 Sep 2009

This may speed it up a bit

Sub test()
Dim LR As Long, i As Long
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
For Each c In Sheets("Sheet2").UsedRange
If c.Value = .Range("A" & i).Value Then
c.Value = .Range("B" & i).Value
c.Font.ColorIndex = 3
End If
Next c
Next i
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?