Please help with VBA code that will do certain things.

[gokou]

I need help creating a VBA code that will scan all columns or a selected column that will look for any number expect for 0 and matches the highlighted color of the cell which is my case is white and when this value is found I would like to have a "1" appended to the next column so we can tell which cells are non 0 values. I will post some pictures about it.

So "excel1.png" is the starting worksheet which has A1 to A20 listed. So the cells of A5-A17 would only be used for this search since its highlighted as white and we do not need to include the highlighted colors.

When the module is ran, it should look like this on excel2.png.

Thank you for the help and I tried to do it myself, but I have tried for several days and no success.

 

[johnnyL]

Something like:

Sub TestColorAndValue()
'
    Dim ColorToCheck    As Long
    Dim cel             As Range
'
    ColorToCheck = -4142                                                                    ' <--- set this to desired color ... -4142 = no color, 2 = white
'
    For Each cel In Range("A1:A20")
        If cel.Value <> 0 And cel.Interior.ColorIndex = ColorToCheck Then cel.Offset(0, 1) = 1
    Next
End Sub

 

[gokou]

Something like:

Sub TestColorAndValue()
'
    Dim ColorToCheck    As Long
    Dim cel             As Range
'
    ColorToCheck = -4142                                                                    ' <--- set this to desired color ... -4142 = no color, 2 = white
'
    For Each cel In Range("A1:A20")
        If cel.Value <> 0 And cel.Interior.ColorIndex = ColorToCheck Then cel.Offset(0, 1) = 1
    Next
End Sub

Thank you so much for that. That worked exactly like what I wanted.

Also, without changing the range like A1:A20 and just select the whole column which there might be thousands of rows within column A, how would I do that instead of replacing it with something like A1:A20000? Lets just say Column A values.

Thank you again!!

 

[johnnyL]

Maybe:

Sub TestColorAndValueV2()
'
    Dim ColorToCheck    As Long
    Dim LastRowColumnA  As Long
    Dim cel             As Range
'
    ColorToCheck = -4142                                                                    ' <--- set this to desired color ... -4142 = no color, 2 = white
    LastRowColumnA = Range("A" & Rows.Count).End(xlUp).Row                                  ' Get last row used in column A
'
    For Each cel In Range("A1:A" & LastRowColumnA)
        If cel.Value <> 0 And cel.Interior.ColorIndex = ColorToCheck Then cel.Offset(0, 1) = 1
    Next
End Sub

 

[gokou]

Maybe:

Sub TestColorAndValueV2()
'
    Dim ColorToCheck    As Long
    Dim LastRowColumnA  As Long
    Dim cel             As Range
'
    ColorToCheck = -4142                                                                    ' <--- set this to desired color ... -4142 = no color, 2 = white
    LastRowColumnA = Range("A" & Rows.Count).End(xlUp).Row                                  ' Get last row used in column A
'
    For Each cel In Range("A1:A" & LastRowColumnA)
        If cel.Value <> 0 And cel.Interior.ColorIndex = ColorToCheck Then cel.Offset(0, 1) = 1
    Next
End Sub

That worked great too. Thank you so much for the help!! I really appreciate your help!

 

[johnnyL]

Glad to help.