+ Reply to Thread
Results 1 to 4 of 4

Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Question Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

    I have a spreadsheet that I have setup to determine what is referred to as side value in gambling terms for the English Premiership. I have attached a values only copy of it to demonstrate what I am trying to and effectively automate, which I think may be able to be done through conditional formatting.

    Essentially all of the other figures on the spreadsheet are either calculated (in the full workbook) through a solver model, formula or in the case of the odds are copied in from another workbook. This particular sheet was just based on the last 100 matches and not the full seasons results in terms of percentages.

    What I am trying to do is in the case of home winning % (column G), where this exceeds home winning odds% (in this case from Bet365 and in column K) - hence creating a side value (spreadsheet model calculates chance of outcome occurring odds exceeding bookmaker odds for outcome occurring) - I would want to highlight the percentage in column G yellow, and the odds in column K another colour (in this case magenta). Can this be done through conditional formatting? In this case I would also like to tie together columns I and L, and also H and M (for the other two possible outcomes). I'm figuring that if this can't be done through conditional formatting, I could probably do this via a Macro that I wouldn't be adverse to.

    Is this possible to do in columns rather to do each cell individually (for the reasons laid out in post 3 - sorry I am editing this from a couple of days after the original post to try and improve the clarity).

    I know I could do this manually (e.g. as I have done here) but it would just be a lot easier if I could automate this and it would eliminate potential mistakes.

    Thanks in advance for any advice,

    2011_2012 Last 100 Snapshot.xls
    Last edited by mrvp; 05-15-2012 at 03:59 PM. Reason: Possibly more appropriate title?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: Conditional Formatting Query for one cell exceeding another and highlighting both

    not sure i understand how your logic works (and it doesnt really matter either, to answer your question)...

    to apply conditional formatting the way you want to, highlight the cells you want to apply it to (or just a single cell, if you want), then select CF, select new rule, formula, then enter a formula like...

    =1sr-cell-reference=2nd=cell-reference

    pick the formatting you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Conditional Formatting Query for one cell exceeding another and highlighting both

    Hi,

    Thanks for this. Sorry I've not been exactly clear (or I'm possibly misunderstanding the answer). I could see that I could do this using two rules for individual cells:

    e.g. rule 1: for Cell G30 if Cell Value exceeds K30 format pattern/background yellow
    rule 2: for Cell K30 if Cell Value is less than G30 format pattern/background magenta

    Can this be done for whole columns at a time rather than just individual cells (I'm only using Excel 2004 for Mac) as I didn't think you could effectively conditionally reference a cell range (which is essentially what I am trying to do, as to do it cell by cell it's just as quick as effectively printing it off and circling the relevant cells - to do it by column I would make a time saving).

    Sorry if that came across wrong and thanks for the idea.

  4. #4
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

    Hi,

    I've changed the title a touch as I thought it was possibly more relevant to what I was trying to achieve. I've still had no joy with this and am considering trying to do a macro as I think that may work (or I think I could possibly add an extra worksheet in and reference to it and do an IF function - I don't really want to change the layout of the sheet in question, as I have already set this up for an easy copy and paste from another workbook).

    Any general advice would be appreciated (and apologies I wouldn't normally bump something).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1