+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting not working when used on cells with formulas

  1. #1
    Registered User
    Join Date
    03-24-2022
    Location
    Bournemouth
    MS-Off Ver
    O365
    Posts
    3

    Conditional formatting not working when used on cells with formulas

    Hi, I am trying to create a database that automatically highlights different colours depending on certain criteria
    Apologies but i cannot link to the workbook as it contains an awful lot of protected data and it would take me a long time to create a version with no data for the purposes of this thread as all the sheets link together in one way or another.

    CF help.PNG

    Row 6 data comes from another sheet based on information in cell E4 drop down.
    I have set up a formula in row 5 =COUNTIF($6:$6,F3) which compares row 6 to row 3 and places a 1 in the cell in row 5 if the data is found in row 3. i.e Child protection lvl1 is in row 6 so throws up a '1' in cell G5. (If there is a better way fo doing this, I am all ears!)

    Based on these findings from the formulas in row 5, I need cells in row 4 to be conditionally formatted as follows:
    - If cell ?5 is blank/empty, then the cell should be grey
    - if cell ?5 has a 1 in it, then it should be white
    THEN
    - if there is a date in cell ?4 then conditionally formatted to highlight the cell red, yellow or green based on the dates proximity to today.

    However, for some reason, I cannot get cells in row 4 to highlight grey if the cells in row 5 are blank/empty, I assume because there is a formula in there and they aren't really blank. I have tried <> instead of =, and ISBLANK but nothing seems to work.
    This is my current set up.

    CF rules.PNG

    Any help would be greatly appreciated.

    I am not the best at excel, i am completely self taught so try and explain it simply if its complicated

    Thanks
    Last edited by Linseyk87; 03-29-2022 at 09:22 AM. Reason: Solved

  2. #2
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    686

    Re: Conditional formatting not working when used on cells with formulas

    Looks like you have the workbook set not not display zeroes? Either way I would think the result of the COUNTIF formula in row 5 would be 0 if it doesn't find anything.

    You will also need to change the "Applies to" range, you might know this already just checking.

  3. #3
    Registered User
    Join Date
    03-24-2022
    Location
    Bournemouth
    MS-Off Ver
    O365
    Posts
    3

    Re: Conditional formatting not working when used on cells with formulas

    Hi nick.williams, thanks for replying. Yes workbook is set to not display '0's as this is required on another sheet in the workbook. Is what i am attempting not possible without this enabled?

    What does the 'applies to' range need to change to? For the example cell selected, this set for formatting would only apply to that one cell as the next cell along, the formula references would change.

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    686

    Re: Conditional formatting not working when used on cells with formulas

    It doesn't stop you doing anything, it just makes it harder to tell the difference between a 0 and a blank cell. With the COUNTIF formula in row 5, if they appear blank they will actually be 0. So you need =0 instead of =""

    You need to change the Applies to range to whatever range you want it to apply to. At the moment it will only affect F4.

    Conditional formatting applies as though you are copying a formula from the top left cell of the range. For example, if you have a formula of =F5=0 applying to $F$4:$G$4, then it will look at F5 when applying to F4 but look at G5 when applying to G4. If you want to always look at the same cell then use the $ in the formula. But how you have it set up looks fine for what you described.

  5. #5
    Registered User
    Join Date
    03-24-2022
    Location
    Bournemouth
    MS-Off Ver
    O365
    Posts
    3

    Re: Conditional formatting not working when used on cells with formulas

    Amazing, its working now I've changed the formula to =0. I knew it would be something as simple as that but I just couldn't figure it.

    Thank you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting not working on some cells
    By tmccar in forum Excel General
    Replies: 4
    Last Post: 02-01-2022, 12:01 AM
  2. [SOLVED] Conditional formatting only working on some cells but not others.
    By BCW! in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-14-2018, 04:13 PM
  3. Replies: 4
    Last Post: 02-16-2016, 03:41 AM
  4. [SOLVED] Conditional Formatting Cells containing formulas
    By sandy1977 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2016, 06:40 PM
  5. Replies: 4
    Last Post: 01-19-2012, 06:56 AM
  6. Replies: 6
    Last Post: 04-29-2011, 09:11 AM
  7. Conditional formatting and formulas not working
    By mshain81 in forum Excel General
    Replies: 6
    Last Post: 02-03-2010, 02:04 PM

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