Results 1 to 9 of 9

Color Entire Row Based On A Value In A Cell

This is a discussion on Color Entire Row Based On A Value In A Cell within the Excel Questions forums, part of the Question Forums category; Hi All, I am presently working on an excel worksheet which has numerous rows. I need to see each cell ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    230

    Default Color Entire Row Based On A Value In A Cell

    Hi All,
    I am presently working on an excel worksheet which has numerous rows. I need to see each cell in column B and if i find the word "Red" in the cell, i need to change the color of the entire row to Red.

    I tried to use conditional formatting for the purpose, however, it didnt work. I guess, conditional formatting works only when the entire cell value is "Red". In my case, the cell value may be something like "I Like Red, Blue and Green Color", or it can also be "Red is my favorite color".

    Please can someone help me design a macro for the purpose.
    Thanks,
    Lonely
    I Came To This World Lonely.. Lived Lonely... And Will Die Lonely!!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: Color Entire Row Based On A Value In A Cell

    Conditional formatting does work with that, you just have to use formulas instead of values:

    Select the entire row that you wish to apply conditional formatting, to, let's say is row 2.

    Go to Format | Conditional formatting...

    Change "Values Is" to "Formula Is" and put this formula

    =ISNUMBER(SEARCH("red",$B2))

    note the relative reference in B2, that is so each cell in row 2 will look at column B to see if it should change the color or not.

    Now click on the pattern button and change the format as you wish.

    That's it !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Posts
    230

    Default Re: Color Entire Row Based On A Value In A Cell

    Hi Juan,
    Thanks for taking time off to answer the problem. However, i was just thinking that it would be really very tricky to tell this to my team members who are not that tech savy.

    Can we do it with a macro.. so that anyone can just run the macro and zoom... the work is done.

    anyways,... thanks again,
    Lonely
    I Came To This World Lonely.. Lived Lonely... And Will Die Lonely!!

  4. #4
    Board Regular
    Join Date
    Mar 2003
    Posts
    215

    Default Re: Color Entire Row Based On A Value In A Cell

    Record a macro folowing the steps given to you by Juan Pablo and this will work. To record a macro Tools - Macro - Record New Macro
    Let the Wookie win

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Posts
    230

    Default Re: Color Entire Row Based On A Value In A Cell

    Hi Wookie,
    I tried to record the macro and use it for the purpose, and esited it. Finally i have something like:
    **********
    Sub formattingmacro()
    Cells.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISNUMBER(SEARCH(""t"",$A1))"
    Selection.FormatConditions(1).Interior.ColorIndex = 33
    Range("A1").Select
    End Sub
    **********
    However, it does not work correctly. It applies the formatting to the cells which dont have the value "t" also.

    Please can you suggest something.

    Thanks,
    Lonely
    I Came To This World Lonely.. Lived Lonely... And Will Die Lonely!!

  6. #6
    Board Regular
    Join Date
    Mar 2003
    Posts
    215

    Default Re: Color Entire Row Based On A Value In A Cell

    This works for me.

    Cells.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISNUMBER(SEARCH(""red"",$B1))"
    Selection.FormatConditions(1).Interior.ColorIndex = 17
    Let the Wookie win

  7. #7
    Board Regular rickyckc's Avatar
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    326

    Default Re: Color Entire Row Based On A Value In A Cell

    Hi,

    What if I want to search the whole sheet....., and if it found the word 'red', example in row 26 (regardless of what column), it will color only A26 to D26 ? What will the vb script be like ?



    Best Regards,
    Ricky

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: Color Entire Row Based On A Value In A Cell

    Try this:


    Sub DoIt()
    ****With Range("A:D").FormatConditions
    ********.Delete
    ********Range("A1").Activate****'Just in case
    ********.Add Type:=xlExpression, Formula1:="=ISNUMBER(MATCH(""*red*"",1:1,0))"
    ********
    ********.Item(1).Interior.ColorIndex = 17
    ****End With
    End Sub
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    Board Regular
    Join Date
    Feb 2003
    Posts
    230

    Default Re: Color Entire Row Based On A Value In A Cell

    Hi Juan,
    Thanks for the message. The code worked well. Just as expected

    Now i am trying to give mt yeam members more control over the formatting. I am looking at adding a form which asks the user to specify the string he is looking at. Then he can also specify the color he wants the rows to be colored with.

    Can this be done... am i asking for something impossible. Anyway, the code till now works absolutely fantastic.
    Thanks again for all your help,
    Lonely
    I Came To This World Lonely.. Lived Lonely... And Will Die Lonely!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com