+ Reply to Thread
Results 1 to 9 of 9

Change Background Color of Row Based on Cell Value

  1. #1
    Registered User
    Join Date
    11-18-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Change Background Color of Row Based on Cell Value

    Hi,
    I want to change the background color of my row according to cell value. For this i have to loop through whole sheet mean say i want to loop through all cell in A say from $A1 to A1000 and where i found word "Absent" row should be red or something like this.

    How can i achieve this in Excel.
    Thanks in Advance

  2. #2
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Change Background Color of Row Based on Cell Value

    Use conditional formatting:
    formula for Cells A1:Z1 "=Instr($A1,"Absent")>0" format color red.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  3. #3
    Registered User
    Join Date
    11-18-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Change Background Color of Row Based on Cell Value

    Thanks for your reply Foxguy..
    I want to write a macro for this thing and i also wrote one and it is working fine.
    Another thing i would like to know is how i can run this macro for all sheets.
    I want to run this thing for all sheets.
    What i have wrote yet is.

    Sub Update_Row_Colors()

    Dim LRow As Integer
    Dim LCell As String
    Dim LColorCells As String
    'Start at row 7
    LRow = 1

    'Update row colors for the first 2000 rows
    While LRow < 200
    LCell = "A" & LRow
    'Color will changed in columns A to K
    LColorCells = "A" & LRow & ":" & "W" & LRow

    Select Case Range(LCell).Value

    'Set row color to light blue
    Case "Loc"
    Range(LColorCells).Interior.ColorIndex = 34
    Range(LColorCells).Interior.Pattern = xlSolid
    Range(LColorCells).Font.Bold = True
    Range(LColorCells).Font.Name = "Arial Narrow"
    Range(LColorCells).Font.Size = 10.5
    Range(LColorCells).EntireColumn.AutoFit

    'Default all other rows to no color
    Case Else
    Rows(LRow & ":" & LRow).Select
    Range(LColorCells).Interior.ColorIndex = xlNone
    Range(LColorCells).Font.Name = "Arial Narrow"
    Range(LColorCells).Font.Size = 10.5

    End Select

    LRow = LRow + 1
    Wend

    Range("A1").Select

    End Sub

  4. #4
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Change Background Color of Row Based on Cell Value

    I can't look at it right now.
    Please put your code inside CodeTags to make it more readable.
    Also Indent code that is inside For...Next loops, If...End If blocks, etc. Much more readable.

  5. #5
    Registered User
    Join Date
    11-18-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Change Background Color of Row Based on Cell Value

    This is the exact code.
    I want to run it on all sheets in excel say i have more than 50 sheets.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Change Background Color of Row Based on Cell Value

    1st:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    I didn't review your code. I just assume it works.
    But it only works on the ActiveSheet. You need to tell it what sheet to work on.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-18-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Change Background Color of Row Based on Cell Value

    It worked like a charm you saved my day.
    Many Thanks ..

    The other thing i like to add in that is i like to delete 1 column for every sheet i tried to put this but it failed


    Please Login or Register  to view this content.
    How do i delete it in multiple sheets.

  8. #8
    Registered User
    Join Date
    11-18-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Change Background Color of Row Based on Cell Value

    Quote Originally Posted by ashishashish8 View Post
    It worked like a charm you saved my day.
    Many Thanks ..

    The other thing i like to add in that is i like to delete 1 column for every sheet i tried to put this but it failed


    Please Login or Register  to view this content.
    How do i delete it in multiple sheets.
    Ok Done with it also.
    Thanks to foxguy for all the help.

  9. #9
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Change Background Color of Row Based on Cell Value

    Don't forget to mark the thread "Solved"

+ 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