Forum Discussion

diseydaly1983's avatar
diseydaly1983
Copper Contributor
May 07, 2020
Solved

Conditional formatting not working

Hi there. Im looking for help with my conditional formatting. Im creating a leave planner and i want all of the same type of leave to be the same colour. So AL in pink AL AM in pink AL PM im pin...
excel
  • mtarler's avatar
    mtarler
    May 11, 2020

    diseydaly1983  Your problem is that the data validation is pointing to the Overview sheet where the value for "FL" is actually "FL " (notice the space at the end).  Then the conditional formatting is set as Value = "FL" and "FL" does not exactly = "FL ".

    I recommend using the "Specific Text" "Starts With" option or "Contains" option

     

mtarler's avatar
mtarler
Silver Contributor
May 07, 2020
sometimes the ranges and cell references can get messed up. double check that the 'applied to' range in the conditional formatting is still correct. otherwise, maybe send the file or screenshots or something for us to go by
diseydaly1983's avatar
diseydaly1983
Copper Contributor
May 10, 2020

mtarlerThanks for your reply, sorry mine is so late, I have a very small baby. I have double checked it against the spreadsheet that actually works and still cant see where I have gone wrong. The only change I have made since it worked is add a column after every column and an overview page to calculate totals.

 

If you could have a look at the attached that would be a great help.

 

Thanks

Diane

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 11, 2020

    diseydaly1983  Your problem is that the data validation is pointing to the Overview sheet where the value for "FL" is actually "FL " (notice the space at the end).  Then the conditional formatting is set as Value = "FL" and "FL" does not exactly = "FL ".

    I recommend using the "Specific Text" "Starts With" option or "Contains" option

     

    • diseydaly1983's avatar
      diseydaly1983
      Copper Contributor
      May 13, 2020
      Thanks very much. Im not great on excel but when i know something can be done i like to try.
      • mtarler's avatar
        mtarler
        Silver Contributor
        May 13, 2020
        glad I could help and it always good to try, because that is how we learn.

Resources