Macro that will check a cell color

[hussong1555]

I need to make a macro that will check the color of a cell in say column "D" and if it is yellow then change the value that is in column "C" of the same row. I have never written a macro before, but the project I am working on has about a 1000 rows and several hundred cells that are highlighted yellow that need to say rev2 on the corrisponding line.

any input would be greatly appriciated.

thank's in advance.

[Palmetto]

This assumes "yellow" is the standard yellow (color index number 6)

Option Explicit

Sub Change_Value_By_Color()

    'declare variables
    Dim c As Range, lrow As Long
    
    'count the number of used rows in column-D
    lrow = Cells(Rows.Count, "D").End(xlUp).Row
        
    On Error Resume Next
    
    'loop thru cells in column-D and test if colorindex = 6 (standard yellow)
    For Each c In Range("D1:D" & lrow)
    
        If c.Interior.ColorIndex = 6 Then
        
            'offset to cell in same row but in column-C and place value into cell
            c.Offset(, -1).Value = "rev2"
            
        End If
    Next c

End Sub

[hussong1555]

palmetto,

you are so awesome, i can't wait to get to work in the morning and try it out. you just turned an all day job into like 20 minutes. I'm really gonna have to learn how to do these macros.

In your leisure, do you think you can explain what you did so I can learn from it. I understand the if statment, but not some of the designations such as: lrow = Cells(Rows.Count, "D").End(xlUp).Row


thanks again

[johnjohns]

You can use excell 2007 for filtering on colors. Also please find the attachment where I have used the custom function getcolor to find the color of a cell. you can find the color of the font also by adding an f in the arguments

[Palmetto]

In your leisure, do you think you can explain what you did so I can learn from it. I understand the if statment, but not some of the designations such as: lrow = Cells(Rows.Count, "D").End(xlUp).Row

Previous post amended with comments to the code.