Forum Discussion

paul1hudson's avatar
paul1hudson
Copper Contributor
Aug 26, 2024

Conditional formatting

Hi, I am trying to set up conditional formatting for a column that looks at two other columns. If the first column is red or amber and the second column is 'No' then I want a red flag to appear. Is this possible? I'm struggling! 

Many thanks in anticipation of help from an expert!

Paul

  • mathetes's avatar
    mathetes
    Aug 26, 2024

    paul1hudson 

     

    You're giving slightly different information between your two postings. So I'm going to have to make a couple of assumptions.

     

    Your original message said that column 1 was red or amber; this latest says red or yellow.

    More significant perhaps, though it depends on how it's coded, your first message said that the second column contains a "No" in order to activate this desired red flag; your latest message says "0"

     

    I'm sorry to be a nit-picker, but the fact of the matter is that the conditional formatting function IS itself very much a nit-picker, which is why it can be tricky to get it doing exactly what you want it to do.

     

    So I've made a couple of assumptions in giving you an example file (attached) where:

    • the date turns red if it's the current month or earlier; yellow if next month, remains white if further in the future.
    • The Paid column (I have two of them) turns red on the basis of a zero or an "N" along with the month being red. It turns yellow if N or zero and the month is yellow. I saw no reason to not stick with the same "warning" color ....  

    Here's what it looks like.  Test it by changing values in columns A, B or C

     

    You can read the conditional formatting rules involved by selecting "Conditional Formatting" either through the "Format" menu across the top of the screen

    OR through the Conditional Formatting icon on the tool bar

     

     

  • mathetes's avatar
    mathetes
    Silver Contributor

    paul1hudson 

     

    What causes the first column to be red or amber? Is it, itself, based on conditional formatting? If so, add the condition of the second column being "No" to whatever the conditions are for that first.

     

    If there's not some conditional formatting causing the first column to be red or amber, what are the reasons for its being red or amber and how does it happen?

    • paul1hudson's avatar
      paul1hudson
      Copper Contributor

      mathetes 

       

      Hi, thank you for your reply!

       

      Yes, the first column would be red or yellow as a result of a condition being met. (A date being this month or next month) A combination of this and whether the other column is 0 (a membership fee hadn't been paid) would, I hope, lead to a red flag.

       

      I'm not sure how I add a No?

       

      Apologies for my lack of knowledge!

       

      Best wishes 

       

      Paul 

      • mathetes's avatar
        mathetes
        Silver Contributor

        paul1hudson 

         

        You're giving slightly different information between your two postings. So I'm going to have to make a couple of assumptions.

         

        Your original message said that column 1 was red or amber; this latest says red or yellow.

        More significant perhaps, though it depends on how it's coded, your first message said that the second column contains a "No" in order to activate this desired red flag; your latest message says "0"

         

        I'm sorry to be a nit-picker, but the fact of the matter is that the conditional formatting function IS itself very much a nit-picker, which is why it can be tricky to get it doing exactly what you want it to do.

         

        So I've made a couple of assumptions in giving you an example file (attached) where:

        • the date turns red if it's the current month or earlier; yellow if next month, remains white if further in the future.
        • The Paid column (I have two of them) turns red on the basis of a zero or an "N" along with the month being red. It turns yellow if N or zero and the month is yellow. I saw no reason to not stick with the same "warning" color ....  

        Here's what it looks like.  Test it by changing values in columns A, B or C

         

        You can read the conditional formatting rules involved by selecting "Conditional Formatting" either through the "Format" menu across the top of the screen

        OR through the Conditional Formatting icon on the tool bar