+ Reply to Thread
Results 1 to 14 of 14

Color code specific cells (on a per row bases) based on textual value within cell

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Question Color code specific cells (on a per row bases) based on textual value within cell

    Example.png

    My use case for this thread is to determine the best method to color code cells based on a value in a given cell in each row. As an example:
    • If a user were to navigate to row 3, column B and input the text "Accept", the cell background color within row 3 (both column D and column F) would turn green.
    • If a user were to navigate to row 3, column D and input the text "Accept", the cell background color within row 3 (for just column F) would turn green. Row 3, column B would be left alone and have a cell background color of white.
    • If a user were to navigate to row 3,(column F) and input the text "Accept", row 3 (both column B and column D) would be left alone and the background color would remain white.
    • The goal is to ultimately have this logic operate within a for loop for each row so that one row is not dependent on another.

    I've started off with the following:

    Dim KeyCells2 As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells2 = Range("H1:H50")

    If Not Application.Intersect(KeyCells2, Range(Target.Address)) _
    Is Nothing Then

    ' Display a message when one of the designated cells has been
    ' changed.
    ' Place your code here.


    For Each cell In KeyCells2
    If cell.Value = "push" Then
    cell.Interior.ColorIndex = 10
    ElseIf cell.Value = " " Then
    cell.Interior.ColorIndex = 2

    End If

    Next cell

    ' MsgBox "Cell " & Target.Address & " has changed."
    End If

    End Sub

    Any guidance or help would be greatly appreciated. Thanks!
    Last edited by Davey19; 09-14-2013 at 11:48 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Not sure I follow all the criteria, but you could do this with the Conditional Formatting feature.
    • Select column D
    • Select from the menu Format\Conditional Formatting
      • Formula is: =AND($B1="Accept", $D1<>"Accept", $F1<>"Accept")
      • Color Pattern: Green
      • OK

    • Select column F and do the same except use this formula
    • Formula is: =OR($B1="Accept", $D1="Accept", $F1="Accept")
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Thanks for the quick response. I already implemented those rules within the conditional formatting manager. The example I gave was only a subset of the number of days that are included within the sheet. I actually have the sheet spanning to 30 days across multiple rows (average if 80). As a result, the increase in the size of the sheet in conjunction with any copy and paste action ends up making the conditional formatting manager approach a very "messy" one. That is ultimately why I'm looking to convert the rules to a vba macro.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    So in a given row you want to highlight green the second column for all the days to the right of the last "Accept" entry? Is that correct?

  5. #5
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Yea......for a given row, the column designated with the text "Accept" and everything to the right of the last "Accept" entry should be highlighted green.

    FYI...I posted a screenshot to illustrate.Example.png
    Last edited by Davey19; 09-14-2013 at 02:11 PM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    This is an event macro that automatically runs when the user makes a change to the worksheet.
    • Right-click on the sheet tab
    • Select View Code from the pop-up context menu
    • Paste the code below in the worksheet's code module


    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Perfect.....many thanks!!

  8. #8
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    One final question, how do I ensure that the cell only includes the term "Accept" vice a column before it which may have a sentence or formula which contains (but not limited) to the word "Accept"?

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Change this...
    Set rngAcpt = r.EntireRow.Find("Accept", , , , 2, 2, 0)

    To this...
    Set rngAcpt = r.EntireRow.Find("Accept", , , xlWhole, 2, 2, 0)

  10. #10
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Ok, so one FINAL question. I've extended the code you provided to encompass "Accept", "Reject" and "Push". The difference is that if a user selects the value "Push", a gradient will appear for a range within the entire row. That being said, if "Push" is de-selected, I would expect the gradient to be removed as well. Unfortunately (based on the code below), the gradient is only removed based on the code within the "For i = 1 To LastColumn Step 2" loop.

    Please Login or Register  to view this content.
    Can you provide some guidance on that?

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Quote Originally Posted by Davey19 View Post
    ... Unfortunately (based on the code below), the gradient is only removed based on the code within the "For i = 1 To LastColumn Step 2" loop.
    When do you want the gradient removed? I don't understand why that's unfortunate.

  12. #12
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    So to clarify, if the value(s) for "Accept" or "Reject" are removed, the highlighted cells (to the right) in turn are removed from the sheet for that row. In contrast, if the value for "Push" is removed, the gradient is not removed for the entire row. Does that make sense?

  13. #13
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Quote Originally Posted by Davey19 View Post
    So to clarify, if the value(s) for "Accept" or "Reject" are removed, the highlighted cells (to the right) in turn are removed from the sheet for that row. In contrast, if the value for "Push" is removed, the gradient is not removed for the entire row. Does that make sense?
    So Push is all or nothing gradient for the entire row?

    If yes...
    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 09-15-2013 at 07:01 PM.

  14. #14
    Registered User
    Join Date
    03-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Color code specific cells (on a per row bases) based on textual value within cell

    Excellent...and yes.

+ 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. change fill color of a range of cells based on color of a cell?
    By DarMelNel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2014, 04:48 PM
  2. [SOLVED] color code cells based on changes to cell value
    By wintermelon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2013, 03:29 AM
  3. Color Cells Automatically based on Value in Specific Column
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-03-2012, 09:54 AM
  4. Replies: 3
    Last Post: 07-08-2010, 06:48 PM
  5. [SOLVED] how to color code a row of cells based on a specific cell value
    By Parker1333 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-02-2005, 04:06 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