Need macro that Populates usage end date in column T

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Query.JPG
 

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,130,173
Messages
5,640,588
Members
417,152
Latest member
DayTimeSeby

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