+ Reply to Thread
Results 1 to 14 of 14

Conditional Formatting to Use a formula to determine which cells to format

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Conditional Formatting to Use a formula to determine which cells to format

    Hi,

    I'm trying to use conditional formatting to apply a rule to determine which cells to format based on data in a cell.

    I have attached a sheet of data that includes the data that would determine which cells are to be formatted, this is column A.

    When any of the data is entered from column A, the corresponding columns show which headers needed to shown.

    Is there a general rule i can use for this or does it need to be seperate for each different data

    Any help would be greatly appreciated
    Last edited by kenadams378; 05-16-2013 at 10:30 AM.

  2. #2
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    ' Bump - No Response required'

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting to Use a formula to determine which cells to format

    I saw your thread in Friday, but:

    1) I had no time.

    2)I was not able to understand which is the expected result and why(I can not understand the logic of the issue)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Apologies,

    I attached the wrong doucment, i have attached the correct one that will make more sense to my requirements.

    The expected result would be, depending on the data in column A would return all cells that required data highlighted.

    Hope this helps

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,440

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Sorry, I dont understand what you are trying to do. Please give more detail, and add a few samples of what your expected outcome is in your workbook
    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

  6. #6
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    What i'm trying to achive is when data is entered into cell A2 it highlights further cells that are required to be populated

    For example if you enter EQT into A2 it will highlight the next cell in red that are required.

    I'm trying to understand how to do this

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,440

    Re: Conditional Formatting to Use a formula to determine which cells to format

    OK but how do we know which cells need to be highlighted - or is it all of them?
    It kinda looks to me like you are already doing that anyway?

  8. #8
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Ok, i'm just trying to understand how the formula & conditional formatting works

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting to Use a formula to determine which cells to format

    The formula that you have in Conditional Formatting rules, does the same job as this one..

    =LEN(CONCATENATE($A2,$B2,$C2,$D2,$E2,$F2,$G2,$H2,$I2,$J2,$K2,$L2,$M2))>0

    Len function count the signs in a cell. So in fact your formula says:

    If any of these cells(a2,b2.......m2) has even one sighn(letter,number or anything..), then highlight with red ALL these cells..


    Edit: Same job like this one..

    =COUNTA($A2:$M2)
    Last edited by Fotis1991; 05-14-2013 at 05:00 AM. Reason: Edit

  10. #10
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Ah ok,

    So if i want to say;

    If cell A2 contains CSH, then highlight these cells(b2,c2,d2 etc etc)

    How would i put this into a formula?

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting to Use a formula to determine which cells to format

    then just

    =$a2="CSH"
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Quote Originally Posted by Fotis1991 View Post
    then just

    =$a2="CSH"


    How do i change the range of highlighted data? if i wanted cells b2 & f2 highlighted for example

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Conditional Formatting>>Manage rules>>Range.....Change the range

  14. #14
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Conditional Formatting to Use a formula to determine which cells to format

    Many thanks for your help

    Much appreciated

+ 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