VBA to replace cell value to zero for columns having Header Date

[Abegail0203]

Hello Excel/VBA People,

I am looking for a vba code that will replace the value of a cell (with yellow color) to zero for those columns having Dates as the Header column only. Example: In a certain excel file there are tabs/sheets that contain the column names Supplier name, Amount, and dates column such as dec-22 jan-23,feb-23 etc. For columns with date as the column header, all the values in yellow cell should be replaced to zero. All other columns such as supplier name, amount with yellow row data, cell values should remain as is. Thank you.

 

[HighAndWilder]

Hello Excel/VBA People,

I am looking for a vba code that will replace the value of a cell (with yellow color) to zero for those columns having Dates as the Header column only. Example: In a certain excel file there are tabs/sheets that contain the column names Supplier name, Amount, and dates column such as dec-22 jan-23,feb-23 etc. For columns with date as the column header, all the values in yellow cell should be replaced to zero. All other columns such as supplier name, amount with yellow row data, cell values should remain as is. Thank you.

Hello Excel/VBA People,

I am looking for a vba code that will replace the value of a cell (with yellow color) to zero for those columns having Dates as the Header column only. Example: In a certain excel file there are tabs/sheets that contain the column names Supplier name, Amount, and dates column such as dec-22 jan-23,feb-23 etc. For columns with date as the column header, all the values in yellow cell should be replaced to zero. All other columns such as supplier name, amount with yellow row data, cell values should remain as is. Thank you.

Are the dates stored as dates formatted as 'dec-22' for example or text?

 

[Abegail0203]

it is formatted as date. dec-22, not text.

 

[HighAndWilder]

it is formatted as date. dec-22, not text.

This code will, for each worksheet where cell A1 has a value of 'Supplier name' and for each column where the value in the cell in row one is a date, change the
value in a cell, if there is one, to zero (0) where the background color (interior color) of that cell is yellow. The color compared is represented by the number 65535.

If the criteria is any different to the above then please let me know. It can easily be changed.

TEST THIS ON A COPY OF YOUR DATA.

Public Sub subZeroValue()
Dim Ws As Worksheet
Dim rng As Range
Dim rngColumn As Range

  ActiveWorkbook.Save

  For Each Ws In Worksheets
  
    If Ws.Range("A1").Value = "Supplier name" Then
                            
      For Each rngColumn In Ws.UsedRange.Columns
        If IsDate(rngColumn.Cells(1)) Then
          For Each rng In rngColumn.Cells
            If rng.Interior.Color = 65535 And Not IsEmpty(rng) Then
              rng.Value = 0
            End If
          Next rng
        End If
      Next rngColumn
            
    End If

  Next Ws
  
End Sub

 

[Abegail0203]

Yes HighandWilder. Its the same logic as to the code you shared above its just that the range is for columns with date as header column. Like if there is feb-23 , mar-23, dec-22 columns, all cells having yellow color will be replaced by zero
Or blank.

 

[HighAndWilder]

Yes HighandWilder. Its the same logic as to the code you shared above its just that the range is for columns with date as header column. Like if there is feb-23 , mar-23, dec-22 columns, all cells having yellow color will be replaced by zero
Or blank.

Try this.

I have taken out the code that checks to see if the cell value is not blank o now blanks are replaced by a zero (0).

Are you saying that the previous code does not recognise the date column headers that you have?

Public Sub subZeroValue()
Dim Ws As Worksheet
Dim rng As Range
Dim rngColumn As Range

  ActiveWorkbook.Save

  For Each Ws In Worksheets
  
    If Ws.Range("A1").Value = "Supplier name" Then
                            
      For Each rngColumn In Ws.UsedRange.Columns
        If IsDate(rngColumn.Cells(1)) Then
          For Each rng In rngColumn.Cells
            If rng.Interior.Color = 65535 Then
              rng.Value = 0
            End If
          Next rng
        End If
      Next rngColumn
            
    End If

  Next Ws
  
End Sub