VBA to colour a cell based on data in 2 others cells

[honkin]

I was asked to repost this question again, so here goes.

How is it possible to format the contents in one column (E), based on the data in 2 other columns (A & C)?

Here is an example of what might be in A & C

A - LTD1
C can have around 20 different leagues. Here's an example
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

What I want is the ability to have the corresponding cell in E change colour based on what shows in A & C

So let's say A10 is LTD1 and C10 is Hungary: NB I, I would want E10 to turn green (RGB 146, 208, 79 which is Hex #92D04F). Since it will not be just a single entry, I assumed just using Conditional Formatting was not an option, but rather VBA.

Predictology-Reports Football Advisor.xlsx
	A	B	C	D	E
2923	LTD2_HOME	13/09/2022 18:15	Qatar: Premier League	LayDraw	Al Gharafa - Al Markhiya
2924	LTD3_AWAY	13/09/2022 18:15	Qatar: Premier League	LayDraw	Al Gharafa - Al Markhiya
2925	LTD2_AWAY	13/09/2022 19:45	England: League One	LayDraw	Ipswich - Bristol Rvs
2926	LTD2_AWAY	13/09/2022 19:45	England: League One	LayDraw	Peterboro - Fleetwood Town
2927	LTD2_HOME	13/09/2022 19:45	England: League One	LayDraw	Wycombe - Accrington
2928	MARIA1_ALTERNATE_HOME_SUMMER	14/09/2022 11:00	Japan: J1 League	LayDraw	Kyoto Sanga - Yokohama F. Marinos
2929	LTD2_HOME	14/09/2022 20:00	England: Championship	LayDraw	West Brom - Birmingham
2930	MARIA1_ALTERNATE_AWAY_SUMMER	15/09/2022 00:00	Uruguay: Primera Division	LayDraw	Nacional - Boston River
2931	MARIA1_ALTERNATE_HOME_SUMMER	15/09/2022 00:00	Uruguay: Primera Division	LayDraw	Nacional - Boston River
2932	LTD1_HOME_SUMMER	16/09/2022 16:00	Lithuania: A Lyga	LayDraw	Kauno Zalgiris - Jonava
2933	LTD1_HOME	16/09/2022 17:30	Bulgaria: Parva Liga	LayDraw	CSKA 1948 Sofia - Septemvri Sofia
2934	LTD6_HOME	16/09/2022 18:00	Germany: 3. Liga	LayDraw	Munich 1860 - Erzgebirge Aue
2935	LTD2_AWAY	16/09/2022 18:30	France: National	LayDraw	Dunkerque - Cholet
2936	MARIA1_ALTERNATE_AWAY	16/09/2022 18:30	France: National	LayDraw	Dunkerque - Cholet
2937	LTD4_HOME	16/09/2022 19:00	NL: Eerste Divisie	LayDraw	Zwolle - Den Haag
Lay The Draw

As indicated, there are maybe 20 leagues just for LTD1. The LTD entries in A go from LTD1 to LTD6, with each having their own set of leagues. Happy to just deal with getting LTD1 right for now, as I can adjust the VBA macro accordingly to create the additional ones required.

So the basic aim is if A contains LTD1 and C contains even just the leagues above, how would I code having E turn green?

Thanks in advance

 

[OaklandJim]

It is not totally obvious to me what is green and what is not. Are you looking to SPECIFY LTD1, say from a dropdown, then cells in column E are green if the value in column A CONTAINS LTD1 (e.g., LTD1_HOME) and if Column C contains one of the leagues specified in your list of leagues above? Please post an example of data that shows what is green and what is not. (In your example data all column E entries are green). If that is correct then only row 2933 in your example would be green and 2932 would not?

Just curious, what data is column A?

 

[honkin]

It is not totally obvious to me what is green and what is not. Are you looking to SPECIFY LTD1, say from a dropdown, then cells in column E are green if the value in column A CONTAINS LTD1 (e.g., LTD1_HOME) and if Column C contains one of the leagues specified in your list of leagues above? Please post an example of data that shows what is green and what is not. (In your example data all column E entries are green). If that is correct then only row 2933 in your example would be green and 2932 would not?

Just curious, what data is column A?

Thanks for reply OaklandJim

So column A is simply a name of a system. Not looking for any dropdowns at all, just a VBA macro which will do what is required. I could even attach that to a button for easy of use

Each system may have up to around 20 leagues in each. Some leagues have performed better than others over a reasonable period and so I want to try and colour code them as they go into the sheet. The green leagues are ones with standout performance, so they are the most important.

I already have some code which looks at the appropriate folder and opens the files in that folder, then copies all of the data to the Lay The Draw sheet and adds the system name into A.

So the aim is to have column E be formatted a particular colour based on what is in both A & C. As indicated, A is simply the system name and C is the name of the league. The leagues names are formatted the way they are by the data supplier, so they come in like Hungary: NB I or England: Premiership or Italy: Serie A

So for the moment, just so I get my head around doing it, I am happy to concentrate on the system with the name LTD1 and the leagues I listed above. Once I have that, it should be no issue to do the next system and its leagues and so on.

So the 13 leagues listed above would be I guess an array for column C with LTD1 being column A. If it means I have to do quite a number of these macros, I can just have them called one after the other to perform the formatting. I am just stuck trying to get started, so am hoping for some guidance

Does that make it a little clearer?

cheers

 

[OaklandJim]

I want to assist but I am having a difficult time understanding the data and especially the result from the XL2BB post that you made. How does code know what is a standout? Maybe provide a link to the workbook that you have so I don't have to recreate all the data and so I might be able to tell what you are trying to accomplish.

 

[OaklandJim]

I guess I'm just brain dead. What you have written still has me confused. I'm pretty sure that I can assist but I have to understand first.

Where are the teams for a system listed? They have to be somewhere. In a different sheet (which makes sense to me as you can list all systems and teams)?

From your data, there is one reference to system LTD1 in row 2933. What comes after that logic wise? How does code know which cells to color green?

Guesses: Column C in row 2933 is Lithuania: A Lyga but that is not one of the teams that you listed for LTD1. Does that mean that if one of the teams listed for LTD1 is in column C column then column E should be green?

 

[honkin]

I guess I'm just brain dead. What you have written still has me confused. I'm pretty sure that I can assist but I have to understand first.

Where are the teams for a system listed? They have to be somewhere. In a different sheet (which makes sense to me as you can list all systems and teams)?

From your data, there is one reference to system LTD1 in row 2933. What comes after that logic wise? How does code know which cells to color green?

Guesses: Column C in row 2933 is Lithuania: A Lyga but that is not one of the teams that you listed for LTD1. Does that mean that if one of the teams listed for LTD1 is in column C column then column E should be green?

cheers OaklandJim

Apologies there and no, not brain dead. I may not have explained it well enough. I really only supplied some sample data, but none had the only system I mentioned, LTD1, but it was just to give examples of how the data is laid out. Many of the other systems are showing, so I will try and clarify below.

So the teams are not overly relevant to what I am trying to achieve. They are listed in column E, which is the column I am wanting to colour. The aim is to reduce the time it takes for me to manually examine the records and show which leagues in each system are the better performers. Those better performing leagues get that allocated colour; kind like giving them 5 stars, if that makes sense. There are other colours, but not worth complicating it.

So at the moment, there isn't any code for colouring E green. It is done manually by me, taking me ages to do, as there are some 10 sheets in the workbook. Matches any one of 2 or 3 other colours, just to signify whether they are top selections or not, or leagues which are only being monitored. Once I have something up and running with a single system, let's say LTD1_HOME, then I will be able to replicate it for any of the systems and leagues. I can then just call them all from a single macro and the sheet will pretty much immediately change colour; at least that is the aim.

The sheet in the workbook is called Lay The Draw

The green colour is just one of the standard greens in the Excel palette; RGB 146, 208, 79 which is Hex #92D04F. I hope that helps

Let me try and simplify this. Below is just one of the systems and a handful of the leagues in that system

Here are those examples.

If A = LTD1_HOME and C = Austria: Erste Liga - E should be coloured green
If A = LTD1_HOME and C = Bulgaria: Parva Liga - E should be coloured green
If A = LTD1_HOME and C = Hungay: NB I - E should be coloured green
If A = LTD1_HOME and C = Poland: Ekstraklasa - E should be coloured green
If A = LTD1_HOME and C = Portugal: Primeira Liga - E should be coloured green
If A = LTD1_HOME and C = Turkey: Super Lig - E should be coloured green

So as I add new matches daily, I simple run the macro and column E changes to the appropriate colour. Most will be green, but some will be a different colour. I guess I will create separate VBA macros for each system and each colour

I hope that helps, but very happy to upload the workbook to a site so it can be viewed.

Thanks in advance

 

[OaklandJim]

There has to be some way to correlate a system with "five star" matchups or teams or whatever. That is why I imagine a data sheet that has the system and then "five star" teams or matchups (whatever they are) listed below the System ID or there is no way to know which should be green.

Book1
	B	C
10	LTD_Home
11	5	Austria: Erste Liga
12	5	Bulgaria: Parva Liga
13	5	Hungay: NB I
14	5	Poland: Ekstraklasa
15	5	Portugal: Primeira Liga
16	5	Turkey: Super Lig
17	4	Team 1
18	4	Team 2
19	4	Team 3
Sheet1

Without that, I just do not understand how code can determine which matchups (or whatever they are) in column C mean that corresponding values in column E are green.

I wish that I am not so confused. Partly because in the sample data provided, there seems no obvious (to me) relationship between what is in columns A and C with values in column E.

 

[honkin]

There has to be some way to correlate a system with "five star" matchups or teams or whatever. That is why I imagine a data sheet that has the system and then "five star" teams or matchups (whatever they are) listed below the System ID or there is no way to know which should be green.

Without that, I just do not understand how code can determine which matchups (or whatever they are) in column C mean that corresponding values in column E are green.

I wish that I am not so confused. Partly because in the sample data provided, there seems no obvious (to me) relationship between what is in columns A and C with values in column E.

Thanks again for your response.

There is actually no need for anything to occur regarding the matchups in E. In fact, column E itself is absolutely irrelevant. It simply lists the match to be played. For the record, though, the teams playing do not matter to the requirement; only the system name and league. Any match being played in the named league under the named system, will be coloured accordingly. The colouring of column E is merely the result of what is in A & C. They are the only columns which matter to the colour formatting of E. The system name and the league are used to determine if the corresponding cell in E is coloured as a preferable match, or not. As indicated, if the system name is as indicated above and the league is also as indicated above, then the corresponding match in E is coloured green.

And you are dead right, there is no relationship between what is in A and C and what is in E. A and C are the only cells which matter. If A & C match the names above, then ANY match in that league is a preferred match, regardless of the team names

I hope that helps mate

cheers

 

[OaklandJim]

I really want to assist but without the exact logic to use to color column E I just do not get it. I can set up code that iterates through values to find a condition and if that condition exists then green.

I repeat my question...There must be a list of leagues for a given system that exists somewhere akin to the data I showed. Otherwise I just cannot understand how to set the green color.

Here is what I imagine using your data for LTD1 and including example data for other systems.

Book1
	A	B	C	D
1	LTD1	LTD2	LTD3	Etc =>
2	Austria: 2. Liga	League 1	League 1
3	Australia: A-League	League 2	League 2
4	Bulgaria: Parva Liga	League 3	League 3
5	Czech Republic: Czech Liga	League 4	League 4
6	Denmark: Superliga	League 5	League 5
7	Germany: Bundesliga II	League 6	League 6
8	Greece: Superleague	League 7	League 7
9	Hungary: NB I	League 8	League 8
10	Portugal: Primeira Liga	League 9	League 9
11	Portugal: Segunda Liga	League 10	League 10
12	Poland: Ekstraklasa	League 11	League 11
13	Qatar: Q League	League 12	League 12
14	Turkey: Super Lig	League 13	League 13
Sheet1

With that data it would be easy to iterate through the matchups data, looking for the respective system in column A then determining whether the league in column C is in the list for the respective system. Otherwise I cannot imagine how to know which row's column E value is green.

Even if we get that ironed out I'm not sure that the code used to color column E green can be used to set the other colors. There has to be some lists akin the one I showed above for system/leagues for code to determine

whether they are top selections or not, or leagues which are only being monitored.

 

[honkin]

I really want to assist but without the exact logic to use to color column E I just do not get it. I can set up code that iterates through values to find a condition and if that condition exists then green.

I repeat my question...There must be a list of leagues for a given system that exists somewhere akin to the data I showed. Otherwise I just cannot understand how to set the green color.

Here is what I imagine using your data for LTD1 and including example data for other systems.

With that data it would be easy to iterate through the matchups data, looking for the respective system in column A then determining whether the league in column C is in the list for the respective system. Otherwise I cannot imagine how to know which row's column E value is green.

Even if we get that ironed out I'm not sure that the code used to color column E green can be used to set the other colors. There has to be some lists akin the one I showed above for system/leagues for code to determine

Cheers OaklandJim

No, no lists, nor any requirement for any lists. When I say no lists, I have a text file which records results in league; just a simple 4 of 7 and so on. Once a league begins to underperform, its colour would change. I just need to stress again, that column E is absolutely irrelevant. Just imagine it is blank and all you want to do is to colour the adjacent cell alongside LTD1_HOME in A and Austria: Erste Liga. The match itself means zip. Whatever the content is in column E will be sitting in a green cell.

This is the beginnings of it that another member wrote in the previous post using an array, but we got no further.

Sub LTD1_HOME_GREEN()
   With ActiveSheet
      .Range("A1").AutoFilter 1, "*LTD1_HOME"
      .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
End Sub

The array would just be expanded to include all the leagues included in LTD1_HOME and a new VBA macro made for each system.

I have written a macro with this in it and then simply put LTD1_HOME in column A at the next available row and Portugal: Primeira Liga in column C, but when I run it, nothing happens to column C, so that was the reason for this post

I'm hoping this is heading in the right direction

cheers