Please help with VBA code that will do certain things.

gokou

New Member
Joined
Mar 30, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Attachments

  • excel1.png
    excel1.png
    28.5 KB · Views: 4
  • excel2.png
    excel2.png
    28.3 KB · Views: 4

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,822
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Something like:

VBA Code:
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

New Member
Joined
Mar 30, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Something like:

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,822
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe:

VBA Code:
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
 
Solution

gokou

New Member
Joined
Mar 30, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Maybe:

VBA Code:
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!
 

Forum statistics

Threads
1,176,138
Messages
5,901,574
Members
434,906
Latest member
Prabhu_Churi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top