Results 1 to 11 of 11

Thread: If statements

  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: If statements

    If have a spreadsheet where I enter in one column if the information is a letter of credit, bond, or check. Can I create an if, then statement which if I type L.O.C. for letter of credit it would either turn all the text in that row red or highlight the text in yellow? Or is the if, then statement only applicable to numerical data? thanks for your help with this

    Chris

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Welcome to the board!
    ====================

    You can use conditional formatting, if I understand what you want:

    Suppose that you column is A that has your choices (LOC, BON, CHE)

    then select entire table, go to FORMAT > CONDITIONAL FORMATTING

    Then in the drop down at the left of the dialog box, choose "Formula Is"

    then put this into the box (be sure that the $ sign is in the appropriate place):

    =$A1="LOC"

    and choose your formatting

    Then for condition 2, do the same, only put this formula into the box:

    =$A1="BON"

    and choose it's formatting.

    If you want a third condition, do the same.

    Click OK.

    If you copy/add rows at the bottom the conditional formatting should apply automatically.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    6

    conditional formatting

    I guess I didn't write my question very well. To give you a better idea I have this spreadsheet which I add to as needed the "K" column identifies if the surety is a bond letter of credit or check. I need for the spreadsheet to highlight the row the L.O.C. is in in yellow if I type in L.O.C. for that project is this possible? I am trying to get around doing conditional formatting everytime I enter a new project

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Actually if you use conditionally formatting (as described, except in your case replace $A1 with $K1), then you can select any line, and hit the Painter button to copy the format, then select as many rows as you need and even though they are blank, any time you add an entry into column K, the line will turn the appropriate color.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Mar 2004
    Posts
    6

    Unhappy

    Sorry not as advanced as you VBA guys but thanks for the help this makes it work real nice, thanks again

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Hope this works for you.

    BTW, there is no VBA involved in this solution.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Mar 2004
    Posts
    6

    here is a question

    Say I want to have mutiple columns within one row return a cell shading of yellow if in column K row whatever has the statement L.O.C.? The formatting works good for one cell for instance using the conditional formatting =$k2="L.O.C." then if I type L.O.C. in that cell the cell turns yellow but what if I cells A2 through P2 to turn yellow if I type L.O.C. in k2 can I do that? thanks for the help

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    As long as you select the columns before you do the conditional formatting. And most crucial, make sure the reference is to: $K1. The dollar sign means that all columns will only check that column for the criteria to be met. But you don't want the dollar sign in front of the number because you want each row to have that reference.. So if your data starts with row 2, then your formula in the conditional formatting dialog box under "Formula Is" will be

    =$K2="L.O.C."

    Note, that if you have selected all the elements of the table prior to opening the Conditional Formatting dialog box, then you only need to reference the first row. Excel assumes you mean that row 3 will be $K3, etc.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Mar 2004
    Location
    Singapore
    Posts
    5

    Cool

    Shades,

    Excel allows only up to 3 conditions. What will the VB script be like if I want more than 3 conditions ?

    Thanks.

    Best Regards,
    Ricky

  10. #10
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    Here is a solution that I found that might help you out.

    http://www.mrexcel.com/board2/viewto...itional+format


    HTH
    texasalynn

  11. #11
    Join Date
    Mar 2004
    Location
    Singapore
    Posts
    5
    Thanks texasalynn for the reply, will check it out later

    Best Regards,
    Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •