Need macro that Populates usage end date in column T

[anand3dinesh]

Dear All,

Please see below image, i have this job i need to do this manually everyday, if this can be automated it makes my life easier.
I need macro that Populates usage end date in column T
i hope i made instructions clear in Image, if anything more reuired please ask.

Many Thanks for your help.
Cheers

 

[anand3dinesh]

anand3dinesh

This VBA code will do what you want under the following conditions:
1. The code is in the tracker workbook. If it is in a different workbook then some additional code will be required to identify the tracker workbook.
2. The layout of the sheet is exactly as is shown in your sample. If any rows or columns are different it won't work.

Sub PopulateColumnT()
  Dim ColorA As Long
  Dim ColorB As Long
  Dim ColorC As Long
  Dim StyleHatch As Long
  Dim ColumnNum As Long
  Dim RowNum As Long
  Dim YearNum As Integer
 
  ColorA = Range("U2").Interior.Color
  ColorB = Range("U3").Interior.Color
  ColorC = Range("U4").Interior.Color
  StyleHatch = Range("U5").Interior.Pattern
 
  For RowNum = 3 To Cells(2, 1).End(xlDown).Row
    For ColumnNum = Cells(2, 1).End(xlToRight).Column To 2 Step -1
      If (Cells(RowNum, ColumnNum).Interior.Color = ColorA Or Cells(RowNum, ColumnNum).Interior.Color = ColorB Or Cells(RowNum, ColumnNum).Interior.Color = ColorC) And Cells(RowNum, ColumnNum).Interior.Pattern <> StyleHatch Then
        Select Case Len(Cells(2, ColumnNum + 1))
          Case 0
            Cells(RowNum, 20) = "1_21"
          Case Else
            If Cells(2, ColumnNum) = 1 Then
              YearNum = Cells(1, ColumnNum)
            Else
              YearNum = Cells(1, Cells(1, ColumnNum).End(xlToLeft).Column)
            End If
            Cells(RowNum, 20) = Cells(2, ColumnNum + 1) & "_" & YearNum - 2000
        End Select
        Exit For
      End If
    Next ColumnNum
  Next RowNum
 
End Sub

it just work fine as i wanted. Thank you very much.

 

[ExcelGzh]

Always glad to be of assistance. Good luck with it all.