You could use
VBA Code:
With ActiveSheet
.Range("A1").AutoFilter 1, "*LTD1*"
.Range("A1").AutoFilter 3, Array("Portugal: Primeira Liga", "Italy: Serie A", "Hungary: NB I"), xlFilterValues
.AutoFilter.Range.Offset(1).Columns(5).Interior.Color = 5296274
.AutoFilterMode = False
.Range("E" & rows.Count).End(xlUp).Offset(1).Interior.Color = xlNone
End With
Howdy Fluff
Sincere apologies with this. I completely lost track of this post and just discovered it again. I have been spending countless hours each week still manually colouring the cells, so a timely discovery I hope.
I think I need to actually name the sheet rather than just saying ActiveSheet, if that makes sense. There are 10 sheets in this workbook and each sheet will need its own VBA to do the colouring.
So what I currently have setup with the workbook is the ability to run a handful of macros which open all the files in the relevant folder for each sheet and copy all the selections t the sheet, adding the system name in the process. That part is great and saves me loads of time. What I'd like now is for the individual macro to then call the VBA for that sheet, automatically colouring the cell with the match details.
This is an example of the macro that handles the LTD model
VBA Code:
Sub Open_All_Files_LTD()
Dim sFil As String
Dim sPath As String
sPath = "/Users/sos/Documents/Football Advisor/New Role/Predictology/Lay The Draw/" 'location of files
ChDir sPath
sFil = Dir("")
Do While sFil <> ""
Workbooks.Open FileName:=sPath & sFil
Call LAY_THE_DRAW_Weekly
sFil = Dir
Loop
End Sub
Once that has finished opening and copying all the matches to the Lay The Draw sheet, I want it to then proceed colouring the matches. The colour I have been using in the sheet is RGB 146, 208, 79 which is Hex #92D04F
Just the LTD model on its own has 36 different systems supplying selections, all with varying number of leagues in them. Do you believe I will need to create a separate VBA module for each of the 36, or is there a way to create a centralised sort of index (not sure if that is the right term, but I'm sure you get the gist)? Like a record listing all the models, leagues and colours.
Here is an example of the leagues just in the LTD1_HOME model which will need to be coloured
Austria: 2. Liga
Australia: A-League
Bulgaria: Parva Liga
Czech Republic: Czech Liga
Denmark: Superliga
Germany: Bundesliga II
Greece: Superleague
Hungary: NB I
Portugal: Primeira Liga
Portugal: Segunda Liga
Poland: Ekstraklasa
Qatar: Q League
Turkey: Super Lig
The names of the leagues are listed exactly as they come in from the data supplier. So with 36 of these models, what would be my best approach to list all the leagues and the colour required (most require the green I listed above, though a handful require RGB 183 222 232 Hex #B7DEE8). All models will be a similar approach.
I don't have an issue creating 36 separate VBA macros (once I can sus the required code) and possibly have something which calls them one after the other, but if there is a way to use a central record of all models and their leagues and colour and that can be referenced in a single VBA macro, happy to go that route instead. I'm just unsure of the best approach.
Thanks so much for your time
