Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a Excel sheet shared by several users. We use vlookup to enter updated data into the sheet, so all users know what has changed. We would like to have any cell that has changed be highlighted in yellow with red font.

I have this code that works, but i don't know how to add yellow fill to the code:

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim ws As Worksheet, ws2 As Worksheet 
    Dim i As Boolean Application.ScreenUpdating = False 'Create Change Log if one does not exist. 
    i = False 
    For Each ws In ThisWorkbook.Worksheets 
        If ws.Name = "Change Log" Then
            i = True 
            Exit For 
        End If 
    Next ws 

    If Not i Then Set ws2 = ThisWorkbook.Worksheets.Add 
        ws2.Visible = xlSheetHidden 
        ws2.Name = "Change Log" 
        ws2.Range("A1") = "Sheet" 
        ws2.Range("B1") = "Range" 
        ws2.Range("C1") = "Old Text Color" 
    Else Set ws2 = Sheets("Change Log") 
    End If 'Store previous color data in change log for rollback. 

    ws2.Range("A1").Offset(ws2.UsedRange.Rows.Count, 0) = Target.Worksheet.Name     
    ws2.Range("B1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Address 
    ws2.Range("C1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Font.Color 'Change font color to red.
    Target.Font.Color = 255 
    Application.ScreenUpdating = True 
End Sub 
share|improve this question
add comment

2 Answers

Add this below Target.Font.Color=255:

Target.Interior.Color = 65535

If you are using the rollback feature from my original post, some additional modification will be needed to store cell fill color history so that the fills can be rolled back. Let me know if this is something you would like to use, and I'll try to post a modified version of the code.

share|improve this answer
add comment

Have you tried the following?

With .Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Applying it to your range of course.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.