+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting in Cells where there are also formulas present.

  1. #1
    Registered User
    Join Date
    04-24-2007
    Location
    Washington, England
    Posts
    14

    Conditional Formatting in Cells where there are also formulas present.

    I want to apply a conditional format to a cell on my workbook which will make the cell blue in the following circumstances a) the cell is displaying no data and b) a certain cell elsewhere on my worksheet does not contain the text "VC" or "NV".

    The problem is that the cell I want to apply this conditional formatting to also contains a formula which automatically populates the cell if a certain cell elsewhere on my worksheet contains the text "VC".

    When I set up my formula within the conditional formatting window it does not work because I think it detects the formula within the active cell and therefore does not regard the cell as not displaying any data.

    The conditional formulas I have been trying to use are;

    Condition 1

    =IF(OR(I18="VC",I18="NV"),"TRUE","FALSE") and

    Condition 2

    =IF(J18<1,"True","False")

    The default cell colour is currently blue and I have set the conditional formatting so that when the conditions of the formula above is true it changes to white.

    Is it possible to do what I am trying to do whilst the formula to prepopulate the active cell is in place.

    Apologies if this is not clear but this is my first post to a forum of this type. If you need any clarification please get in touch.

    Thank you.
    Last edited by m0llydog; 04-24-2007 at 04:12 AM.

  2. #2
    Forum Contributor
    Join Date
    02-23-2005
    Location
    England
    Posts
    110
    I think you have a few problems :

    Firstly, in your explanation, you say that you want to have the cell blue when both 'conditions' are true. Then you use two of the Conditional Formatting Conditions to achieve this. You should only use one Conditional Formatting Condition :

    =IF(AND(OR(I18="VC",I18="NV"),(J18<1),"TRUE","FALSE")

    However, in place of J18<1, I would use OR(J18="",J18=0). Probably a matter of preference, but your use of J18<1 will be true if J18 has a value e.g. 0.5.

    Next you are using a default colour of blue when you should, I believe, be using a default colour of white. The Conditional Formatting should be set to give a cell colour of blue.

    Finally, my understanding is that the only reason that a formula in the cell you want conditional formatting would cause the conditional formatting to not set the colour to blue would be because the cell is no longer empty.

  3. #3
    Registered User
    Join Date
    04-24-2007
    Location
    Washington, England
    Posts
    14
    Thanks for the info.

    Your suggestion made reconsider my approach to this problem and I think as a result I may have cracked it!

    Instead of looking negatively for entries which should not trigger the condition, I turned the problem around so that I was looking for the entry which would trigger the condition and as a result have used the following formula in the conditional formatting which seems to be working.

    =IF(AND(I18="VX",J18=" "),"True","False").

    I also ensured that the formulas were in the cell before I added the conditional formatting (cf), because it appears to me that if you insert the cf first then add the formula it makes the cf respond "false" as it assumes that there is something now something in the cell.

    Thanks again.

  4. #4
    Forum Contributor
    Join Date
    02-23-2005
    Location
    England
    Posts
    110
    Glad you worked it out.
    Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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