Conditional Format based on value in corresponding cell

  • Hi,
    I want to do a formula that looks at the value in one cell and then if the one in the next cell is a certain value the cell colour needs to be yellow, green or blue.


    So for example if the FSP is 4 and the target should be 1 so would be green, however if its 2 its better so would be blue and less than 1 would be below so yellow.


    The FSP levels and what they would be if on target are


    3 or 4 should equate to a 1a, 1b or 1c, 5 should be a 2c, 6 would be a level 2b, 7 a Level 2a and 8 a level 3.


    Hope that makes some sense and someone is able to make head or tail of it,

    Thank you in advance.


    [TABLE="width: 192"]

    [tr]


    [TD="class: xl66, width: 64, bgcolor: #F2F2F2"]FSP
    [/TD]
    [TD="class: xl66, width: 64, bgcolor: #F2F2F2"]Target
    [/TD]
    [TD="class: xl66, width: 64, bgcolor: #F2F2F2"]Actual
    [/TD]

    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]4
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2b
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2c
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]5
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]1a
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]1a
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]5
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]6
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]3b
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]7
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2a
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]6
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2b
    [/TD]
    [TD="class: xl65, bgcolor: transparent"]2c
    [/TD]

    [/tr]


    [/TABLE]

  • Re: Conditional Format based on value in corresponding cell


    Hi there Plumwood, I've dealt with a lot if conditional formatting. I'm not sure if you will need this bit of wisdom but once you have one sheet completed and "if" you have multiple sheets , you can copy and paste just the CF to the other columns You may need to change the column letter for example if it's in F and you copy to N change the F to N.


    Good Luck!

  • Re: Conditional Format based on value in corresponding cell


    I've tried reading your post (again) - but still can't make sense of it and not going to assume.


    Please upload a workbook with cells formatted manually to fit the scheme you want, and comments as to why each cell is a particular colour...

  • Re: Conditional Format based on value in corresponding cell




    Im on my iPad at the moment but when I get to my desktop I will look at the CF tool bar and direct you if cytop hadn't already. It can be done simply with the =>< commands and you would format (color shade) your cells how you want them. Do sorry, just can't remember which statement to use If i do I will post it

  • Re: Conditional Format based on value in corresponding cell


    Do you still need help with this? If so, upload a spreadsheet and I will give you a hand


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!