+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting apply to 1 Column

  1. #1
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2019
    Posts
    209

    Conditional Formatting apply to 1 Column

    Hi,

    I have issue with conditional formatting - where there are two trigger to change colour

    e.g if Cell C9 = A and Cell C8 <> 0 then Fill red colour in cell C3 to C7.
    is that possible?

    Formula i use
    Please Login or Register  to view this content.
    but it does not work

    anyone can help?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Conditional Formatting apply to 1 Column

    Hi lexusap- Try:
    =AND($C$9="A",$C$8<>0)

    When you paste, make sure there's no space in front of the "=" sign. That's an issue with this website sometimes.

    Also, in your sample worksheet, C8 is set to 0, so the formula yields False and nothing happens. Change C8 to 1.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 06-09-2017 at 02:16 AM.

  3. #3
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2019
    Posts
    209

    Re: Conditional Formatting apply to 1 Column

    I did, But what is the Apply formatting Area?

    Thanks

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Conditional Formatting apply to 1 Column

    Didn't you say C3:C7? Select those cells, then click Conditional Formatting. Paste This:

    =AND($C$9="A",$C$8<>0)

    When you paste the formula, make sure there's no space in front of the "=" sign. That's an issue with this website sometimes.

    Also, in your sample worksheet, C8 is set to 0, so the formula yields False and nothing happens. Change C8 to 1.


    Untitled.png Untitled2.png
    Last edited by leelnich; 06-09-2017 at 02:34 AM.

  5. #5
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2019
    Posts
    209

    Re: Conditional Formatting apply to 1 Column

    Hi LeelNich,

    Thanks for the formula - it works well. Now i need to know how to apply them for the whole column.

    e.g (If C9="A" , C9<>0) apply format area C3 to C7,
    if C16 = "A", C15<>0) apply format area C10 to C14
    If C23 = "A", C24<>0) apply format area C17 to C21

    and so on...

    Thanks
    Lex

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting apply to 1 Column

    lexusap a few pointers when using CF...
    1. CF only works on TRUE (or 1) and FALSE (or 0), so you only need to construct the formula to return 1 of them.
    Instead of...
    =IF(AND(C9="A",C8<>0),TRUE,FALSE)
    you only need to use...
    =AND(C9="A",C8<>0)
    2. Avoid using full-column references when using CF, it can slow your file down. rather, just use the range you need (or 2-3 times that much)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2019
    Posts
    209

    Re: Conditional Formatting apply to 1 Column

    Will set up conditional formatting as required rather than whole columns. I have an impression that the whole column should work faster than individual CF.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting apply to 1 Column

    You wouldnt have individual CF's, but try and keep the ranges restricted

+ 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. [SOLVED] Codes to Apply Conditional Formatting on Entire Column
    By chasoe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-08-2015, 12:21 AM
  2. [SOLVED] Using VBA to apply Conditional Formatting based on a calculation to Column
    By tonygg9 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-12-2014, 09:48 PM
  3. Conditional formatting if in range of values: apply to column
    By marcopietro in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2014, 02:34 PM
  4. Replies: 5
    Last Post: 06-28-2014, 02:53 AM
  5. VBA for Conditional Formatting - apply a colour based on conditions in each column.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-27-2013, 01:24 PM
  6. Replies: 5
    Last Post: 02-28-2012, 04:47 PM
  7. How to apply conditional formatting to a whole column
    By scottghansen79 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2010, 05:21 AM

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