Fantasy Football Macro

[TonyforVBA]

Hopfully people will see what I am trying to do here from the attached.
But basically I want to set up conditional formating on the 'Macro' sheet so that each week it will base the formating of the cells against the Home & Away form of both teams plaing in each match.

Would anybody have an ideas what might be the best way to go about this?


ps. SO happy the football is back!!

[TonyforVBA]

I should probably embelish a little bit more on this.

Eg. Arsenal are to play at home to Sunderland in GameWeek 1 (GW1)
We can see this on (Macro, C, 8).

If you check Arsenals Home form against Sunderland's away form on the 'Stats' sheet. You can see that they are 6.3 & 2.1, respectivley.

In this case as Arsenal has a higher form than Sunderland, I would want Call (Macro, C, 8) to be GREEN
and Cell (Macro, C, 21) to be RED.

[Jester0001]

By the naming convention of the sheet "Macro" and the title of this post, I'm guessing that you want a macro to conditionally format cells. However, after looking at the sheet, I don't believe you need a macro for your conditional formatting.

Here is a link to help you with conditional formatting in Excel 2003:
http://spreadsheets.about.com/od/exc...itn_format.htm

Also, I'm not sure which sheet has the Home & Away data you're talking about. If you tell me exactly which cell(s) you want conditionally formatted, and they type of formatting, along with the conditions then I can help give you more insight.

---------- Post added at 11:12 AM ---------- Previous post was at 11:11 AM ----------

Just saw your 2nd post after I posted. I'll take a look and brb

[Jester0001]

Ok, you will need to use the VLOOKUP formula in your conditional formatting.

Try this formula in the conditional formatting dialog for cell C8 on sheet 'Macro'

Please Login or Register  to view this content.

and set the format to the color Green.

Then, do the same thing and put in this next formula for the same cell on the conditional formatting dialog:

Please Login or Register  to view this content.

and set the format to the color Red.

Then, you will just need to copy the conditional formatting to the other cells, referencing the correct cells in the formula.

One problem I see that you may run into is the the data in your data table under the GW1, GW2, etc. don't exactly match the data in the row headers on sheet 'Stats'. For example, the text "WHU(a)" in cell C9 on sheet 'Macro' doesn't have a compliment in the sheet 'Stats'. I see that there is a "WHU" on sheet 'Stats', but no "WHU(a)". You will need to either edit the values in your 'Macro' sheet to match the lookup values in the 'Stats' sheet, or we will have to figure out something else, which can be more problematic.

Let me know if you are able to get it working.

[TonyforVBA]

Jester, Thanks for your help with this.

I've updated the attached so it is easier for me to explain things. I've also attemped some Conditional Formatting within cell F8 'Macro'.

Looking at all the data on the Macro sheet now I want 2 pieces of conditional formatting.
One for when a team plays a home game and a 2nd for when a team plays away.

If we take cell F8 for example. This is Arsenal playing at home to Sunderland. in this cell I need to apply a formula that does a vlookup for 'SUN' within columns C:D and then takes the Away for for SUN 'B21' (as these will be the team who are playing away).

The formatting then needs to work out if 'B21' (Sunderlands away form) is greater or less than 'A8' (Arsenal's home form), and apply the correct formatting accordingly.

Here is my Conditional Format code. But at the moment it is not working for me.


Condition 1

Please Login or Register  to view this content.

Then RED

Condition 2

Please Login or Register  to view this content.

Then Green


Any help on this would be much apreciated.

Thanks

[TonyforVBA]

Find Attached