Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 76

Thread: Colour Entire Row Base on the Value of Two Other Cells

  1. #1
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location

    Colour Entire Row Base on the Value of Two Other Cells

    Hi,
    I'm having problems with conditional formatting. Conditional Formatting using the built in Excel formatting seems to require very long formulas for what I wish to achieve. I was hoping to get help coding with VBA instead.
    I have attached a more exact example of my problem. Basically I want to colour each row based on the values in row "G" and "O'. They can be Red, Green, Blue or Black.
    Cond Format.jpg
    Below is my Excel Conditional Format effort for most of the conditions but does not include the ISBLANK in most cases. I am hoping that VBA will be simpler.

    =IF(AND(ISBLANK($G$11),ISBLANK($O$11)),TRUE,FALSE)   
    =IF(AND(($G$11<=20),ISBLANK($O$11)),TRUE,FALSE)
    =IF(AND(AND(($G$11>20),( $G$11<=50)),ISBLANK($O$11)),TRUE,FALSE)
    =IF(AND(AND(($G$11>50),( $G$11<=100)),ISBLANK($O$11)),TRUE,FALSE)
    =IF(AND(AND(($G$11>100)),ISBLANK($O$11)),TRUE,FALSE)
    =IF(AND(ISBLANK($G$11),( $O$11<=7)),TRUE,FALSE)
    =IF(AND(($G$11<=20),( $O$11<=7)),TRUE,FALSE)
    =IF(AND($G$11>20, $G$11<=50, $O$11<=7),TRUE,FALSE)
    =IF(AND($G$11>50, $G$11<=100, $O$11<=7),TRUE,FALSE)
    =IF(AND($G$11>100, $O$11<=7),TRUE,FALSE)
    =IF(AND(ISBLANK($G$11),( $O$11>7), $O$11<=30),TRUE,FALSE)
    =IF(AND($G$11<=20, $O$11>7, $O$11<=30),TRUE,FALSE)
    =IF(AND($G$11>20, $G$11<=50, $O$11<=30),TRUE,FALSE)
    =IF(AND($G$11>50, $G$11<=100, $O$11>7, $O$11<=30),TRUE,FALSE)
    =IF(AND($G$11>100, $O$11>7, $O$11<=30),TRUE,FALSE)
    =IF(AND(ISBLANK($G$11),( $O$11>30), $O$11<=60),TRUE,FALSE)
    =IF(AND($G$11<=20,$O$11>30, $O$11<=60),TRUE,FALSE)***
    =IF(AND($G$11>20, $G$11<=50, $O$11>30, $O$11<=60),TRUE,FALSE)
    =IF(AND($G$11>50, $G$11<=100, $O$11>30, $O$11<=60),TRUE,FALSE)
    =IF(AND($G$11>100, $O$11>30, $O$11<=60),TRUE,FALSE)
    =IF(AND(ISBLANK($G$11),( $O$11>60)),TRUE,FALSE)
    =IF(AND(($G$11<=20),( $O$11>60)),TRUE,FALSE)
    =IF(AND($G$11>20, $G$11<=50, $O$11>60),TRUE,FALSE)
    =IF(AND(($G$11>100),( $O$11>60)),TRUE,FALSE)
    =IF(AND($G$11>50, $G$11<=100, $O$11>60),TRUE,FALSE)
    The columns with "TRUE" or "FALSE", only represent the formulas that I would use for the formatting. In reality they would be TASK's i.e. "Replace Oil Filter"

    Many Thanks in Advance
    Attached Images Attached Images

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,140
    Location
    If you were to attach a workbook, I'm sure it would be a whole lot easier for members to understand the concept. At the moment you have 25 IF statements and no indication of which cells they apply too.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location

    Example of Excel Conditional Formatting Spreadsheet

    Sorry, i didn't realise I could upload an Excel file. That's why there was only an image in my last post. I hope this attached file helps to explain my problem.
    I realise the code I have posted is for the Excel Conditional Formatting and would really prefer to go to VBA if possible.

    Thanks for any help in advance.
    Attached Files Attached Files

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,140
    Location
    I'm still struggling to follow the logic of the data you have provided. One of the guidelines in your workbook suggests that you are seeking to colour a target line based on the amount of time left to complete a task, yet it somewhat difficult to work within the data.

    For example, What's the relationship between the Days and Hours data? Within the first few rows you have nil days but 20 hrs = Red, 21 hrs = Green, 100 hrs = Blue, & 101 hrs = Black If everything was converted to hours then a simple Case Select could be used.

    Next example, a few rows further down all have 7 days allocated but the hours vary between blank & 101hrs. Which is the more determine data?

    If this was my worksheet, I'd convert all time (Days & Hours) to one common format, then I have an auto sort each time the workbook was opened, ranking all tasks from minimum to maximum time frames. At the moment this is far more complicated than necessary
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,776
    Location
    Correct this please.

    Hours=0,Days=0 BLACK
    Hours<=20,Days=0 RED
    Hours>20, <=50,Days=0 GREEN
    Hours>50, <=100,Days=0 BLUE
    Hours>100,Days=0 BLACK
    Hours=0, Days<=7 RED
    Hours<=20, Days<=7 RED
    Hours>20, <=50, Days<=7 RED
    Hours>50, <=100, Days<=7 RED
    Hours>100, Days<=7 RED
    Hours=0, Days>7,<=30 GREEN
    Hours<=20, Days>7,<=30 RED
    Hours>20, <=50, Days>7,<=30
    Hours>50, <=100, Days>7,<=30 GREEN
    Hours>100, Days>7,<=30 GREEN
    Hours=0, Days>30,<=60 BLUE
    Hours<=20,Days>30, <=60 RED
    Hours>20, <=50, Days>30,<=60 GREEN
    Hours>50, <=100, Days>30,<=60 BLUE
    Hours>100, Days>30,<=60 BLUE
    Hours=0, Days>60 BLACK
    Hours<=20, Days>60 RED
    Hours>20, <=50, Days>60 GREEN
    Hours>50, <=100, Days>60 BLUE
    Hours>100, Days>60 BLACK



    Select Case Hours + (Days * 24)
    Case 0: Tint = Black
    Etc
    End Select
    Cell.Interior.Color  = Tint
    Last edited by SamT; 06-28-2015 at 07:30 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,527
    A CF formula can't look like:

    =IF(AND(ISBLANK($G$11),ISBLANK($O$11)),TRUE,FALSE)
    but only
    =AND(ISBLANK($G$11),ISBLANK($O$11))

  7. #7
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location

    Example of Excel Conditional Formatting Spreadsheet

    Hi and thanks for the kind help.

    I've uploaded a modified spreadsheet that will hopefully explain what I'm trying, rather in vain, to achieve.

    There are comments above the Columns which may clarify things. You will see that the only important Columns are the TASK column "A" , the HOURS column "G" and the DAYS column "O". All the other columns are to help me understand where I am and try to get my head around the problem. An important point to note is that HOURS are "running hours" not calendar hours.

    snb.
    Thanks for correcting my incorrect CF formula. I really do not want to use CF in Excel but rather VBA. The CF formulas where my first attempt at resolving my problem but I soon started to get lost when trying to apply them. If you change any of the values in the sample date HOURS or DAYS column, the cells do change TRUE and FALSE.

    I hope this brings a little more clarity to my request.
    Thanks again.
    Attached Files Attached Files

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,776
    Location
    In your latest book, the colors of the tasks are not dependent on hours or days, (See A21, A26)

    You have positive hours and negative Days. WTF? (see A16, A21, )

    Your notes say that these are hours and days " Remaining to the associated TASK." But you have a situation (Row 12) where the task is 100 hours overdue, but there are 60 days remaining. On the other hand (Row 21) the task has 21 hours remaining, even though it is 2 days overdue.

    But at least now we can see that there are two sets of color rules

    Hours IsEmpty Black
    Hours<=20 Red
    Hours<=50 Green
    Hours <=100 Blue
    Else (> 100) Black

    Days IsEmpty Black
    Days <= 7 Red
    Days <=30 Green
    Days <=60 Blue
    Else (>60) Black

    Since you want the Task colored according the which time frame has the highest priority (Days = 5 is higher priority than hours = 21)
    Is that right? I won't continue coding until I know.

    Why not use 24 hour ranges for the hours, ie, 24, 48, and 96, instead of 20, 50, and 100?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,599

  10. #10
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Hi SamT, Logic for my Colours are as follows:

    HOURS
    RED <=20 Less than or equal to 20
    GREEN >20<=50 Greater than 20 and less than or equal to 50
    BLUE >50<=100 Greater than 50 and less than or equal to 100
    BLACK >100 Greater than 100
    BLACK ISEMPTY just for logic

    DAYS
    RED <=7 Less than or equal to 7
    GREEN >7<=30 Greater than 7 and less than or equal to 30
    BLUE >30<=60 Greater than 30 and less than or equal to 60
    BLACK >60 Greater than 60
    BLACK ISEMPTY just for logic.

    I plan to Autofilter tasks based on HOURS and then tasks based on DAYS to help me decide which order to carry out work.

    HOURS (running hours not calendar. If the vehicle is not in use no hours accumulated)

    Negative numbers indicates a vehicle has missed an inspection and run over either running hours or calendar date because the operator didn't bring it in for maintenance due to negligence or the maintainer did not pay attention to this spreadsheet.

    Some tasks have both running time and calendar limits. The important thing is that an inspection that has just gone RED will fall soon, even though the same task based on calendar may be BLUE and not yet due. It's a case of whichever comes first, hours running time or days.

    I hope this helps explain and many thanks for your time.
    Last edited by mexmike; 06-28-2015 at 03:13 PM. Reason: Missed out HOURS heading

  11. #11
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Please excuse my ignorance regarding cross posting. I guess I have not read posting rules thoroughly enough.

  12. #12
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,776
    Location
    Now it makes sense. Do you want Tasks, Hours and Days colored?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #13
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Hi SamT,
    It would be great if that was possible. I would colour each according to their current status.
    Thanks for the assistance.

  14. #14
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,776
    Location
    The Functions in Module1 are pretty generic, they don't care where the cell is. I set up the code structure so that it is easy to add a priority level, if, for instance, you decide that what is red should be yellow and negative numbers should be Red.

    The code in sheet1:

    If you ever change the layout of your Tasks sheet, be sure that the Constants at the top of the code match the new column layout.

    The Worksheet_change Sub: If you don't understand the comments, ask, I am not always clear in my instructions.

    The SetColors Sub: It works, should'nt need to mess with it. Except, I added a fillip at the very bottom to set the Font in the Tasks Column to Bold, if it was Red. You can't miss it. If you don't like it, just comment out that IF...Then line.

    The SetColorsManually Sub is only needed when your Tasks sheet has never had it's colors set or when the whole thing needs updating. To run it, place the mouse cursor inside the sub and press F5.

    Module1 Code
    Option Explicit
    
    Enum Priorities
      Priority0
      Priority1
      Priority2
      Priority3
    End Enum
    Function PriorityDays(Cel As Range) As Long
        If Cel.Value = "" Then
        PriorityDays = Priority0
        Exit Function
      End If
      
    Select Case Cel.Value
      Case Is <= 7: PriorityDays = Priority3
      Case Is <= 30: PriorityDays = Priority2
      Case Is <= 60: PriorityDays = Priority1
      Case Else: PriorityDays = Priority0
    End Select
    End Function
    Function PriorityHours(Cel As Range) As Long
      If Cel.Value = "" Then
        PriorityHours = Priority0
        Exit Function
      End If
      
    Select Case Cel.Value
      Case Is <= 20: PriorityHours = Priority3
      Case Is <= 50: PriorityHours = Priority2
      Case Is <= 100: PriorityHours = Priority1
      Case Else: PriorityHours = Priority0
    End Select
    End Function
    Function ColorByPriority(priority As Long) As Long
      Select Case priority
        Case 0: ColorByPriority = vbBlack
        Case 1: ColorByPriority = vbBlue
        Case 2: ColorByPriority = vbGreen
        Case 3: ColorByPriority = vbRed
      End Select
    End Function
    Sheet1 Code
    Option Explicit
    
    'Edit all to suit
    Const TaskCol As Long = 1
    Const HoursCol As Long = 7
    Const DaysCol As Long = 15
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TrackChanges As Range
    
    'TrackChanges must be set to those ranges that are manually changed.
    'If the values in Days and Hours Remaining  are the result of formulas, then
    'define these ranges as the locations where the new values of the
    'formula precedents are entered
    Set TrackChanges = Range(Range(Cells(11, TaskCol), Cells(10000, TaskCol)). _
                            Range(Cells(11, HoursCol), Cells(10000, HoursCol)), _
                            Range(Cells(11, DaysCol), Cells(10000, DaysCol)))
    If Intersect(TrackChanges, Target) Is Nothing Then Exit Sub
    
    SetColors Target
        
    End Sub
    Private Sub SetColors(Cel As Range)
    Dim TimePriority As Long
    Dim DatePriority As Long
    Dim TaskPriority As Long
    Dim Rw As Long
    
    Rw = Cel.Row
    TimePriority = PriorityHours(Cells(Rw, HoursCol))
    Cells(Rw, HoursCol).Font.Color = ColorByPriority(TimePriority)
    
    DatePriority = PriorityHours(Cells(Rw, DaysCol))
    Cells(Rw, DaysCol).Font.Color = ColorByPriority(DatePriority)
    
    If DatePriority > TimePriority Then
     TaskPriority = DatePriority
    Else
     TaskPriority = TimePriority
    End If
    
    With Cells(Rw, TaskCol).Font
      .Color = ColorByPriority(TaskPriority)
      .Bold = False
       If TaskPriority = Priority3 Then .Bold = True
    End With
    
    End Sub
    
    'Run once to set all tasks
    Private Sub SetColorsManually()
    Dim Cel As Range
    For Each Cel In Range("A11:A" & Cells(Rows.Count, 1).End(xlUp).Row)
      SetColors Cel
    Next Cel
    End Sub
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  15. #15
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,527
    I'd use:

    Daypriority

    ="priority "&CHOOSE(MATCH(INT(TODAY()-A12);{-1;0;7;30;60;90};1);"red";"orange";"blue";"green";"yellow";"black")
    Hourpriority
    "hourpriority "&choose(MATCH(24*(C1-MOD(NOW();1));{-1;0;20;50;100;120};1);"red";"orange";"blue";"green";"yellow";"black")
    Last edited by snb; 06-29-2015 at 01:59 AM.

  16. #16
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Dear SamT,

    Amazing work. I'm quite speechless at your effort. Cleary I would have spent the next half century trying to achieve this result.

    Many many thanks.

    Snb, Many thanks also for your input, now that have an idea of what's involved, I'll try your suggestions as well.

    Once again. Many many thanks to all. Very inspiring and extremely educational.

    Kind Regards.




  17. #17
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Hi SamT and snb,

    I followed your advice regarding the negative numbers by adding a Case; worked a treat!
    Also, I changed from the vbgreen to
    Case 2: ColorByPriority = RGB(32, 148, 68)
    as the vbgreen colour is a little too bright.

    I had integrated your code into my working spreadsheets, which are way more complex than my example spreadsheet, expecting trouble as they already have VBA code. Guess what not a single problem. Everything worked exactly as advertised. Also, as you quite rightly state, it is quite universal and can be applied to a wide range of spreadsheets not just my application.

    Great work, really! I owe you a beer or three when you next come through Trinidad where I'm based.

  18. #18
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,776
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #19
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Thanks for offering to clarify comments that you made in your coding.

    Regarding this; I do use formulas to calculate hours and days remaining but do not understand, due to ignorance'...
    "If the values in Days and Hours Remaining are the result of formulas, then'define these ranges as the locations where the new values of the formula precedents are entered".
    My Days remaining formula, is based on a cell with
    =NOW()
    , periodicity in days and date last completed.
    HOURS remaining comes from a formula based on single cell with current total vehicle hours, hours at which an inspection was completed and periodicity at which an inspection occurs i.e. Oil change 200 Hours, done at 789 hours. I update the total vehicle hours each day and of course, the calendar rolls by on it's own, so no need to input the date each day.


    Also, my TASKS are selected from a drop down list on each row "A". Once I pick a new task and add periodicity for HOURS and DAYS, and hours and date completed at etc , I'd like the individual row colours to update at that time if possible.

    These final details would allow me to not go into VBA to update the colours as new TASKS are entered onto the sheet.

    Once again thank you for your kind assistance.

  20. #20
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,776
    Location
    This is the code where the procedure is Triggered

    Set TrackChanges = Range(Range(Cells(11, TaskCol), Cells(10000, TaskCol)), _ 
        Range(Cells(11, HoursCol), Cells(10000, HoursCol)), _ 
        Range(Cells(11, DaysCol), Cells(10000, DaysCol))) 
     If Intersect(TrackChanges, Target) Is Nothing Then Exit Sub
    Taking it line by line
    the first part "Set TrackChanges = Range" is the Assignment.

    This line defines the range that is the top 10000 rows of column A, (Column number 1.) Note that it is a Range from one Cell to the next. The Space Underscore at the end is the Line Continuation code.
    (Range(Cells(11, TaskCol), Cells(10000, TaskCol)), _
    The next Line Defines the Hours column. Like all three Range definitions, it uses predefined Constants for the column Number. You can use the Column Letter(s) String in place of the Column Number. (Cells(11, "H"),) Using the Column Number is just my style. The third line is the same for the Days column
    Range(Cells(11, HoursCol), Cells(10000, HoursCol)), _
    the final line say that if the changed cell was not in the Defined Range "TrackChanges," then Don't run the sub anymore.
     If Intersect(TrackChanges, Target) Is Nothing Then Exit Sub
    Right now the Procedure will only be run when you add a new Task, which is before the Remaining Days and hours are present. Not good.
    Once I ... add periodicity for HOURS and DAYS, and hours and date completed at etc , I'd like the individual row colours to update at ... [those Events] if possible.
    I anticipated this, that is why the Sub SetColors only uses the Cell Row. Any Cell can be used as a trigger as long as it is in the same Row as the Task. At different times you will be making two changes at the same time. This will trigger the procedure at each change. The "EnableEvnts" mentioned in some code below will keep this from being a problem.

    In the Sheet Code:
    Remove these lines
     'Edit all to suit
    Const TaskCol As Long = 1 
    Const HoursCol As Long = 7 
    Const DaysCol As Long = 15
    and Replace them with these. Put the appropriate column letters in between the quote marks.
    'edit all to suit
    Const HoursPeriodCol As String = ""
    Const DaysPeriodCol As String = ""
    Const TimeCompleteCol As String = ""
    Const DateCompleteCol As String = ""
    In the Worksheet_change sub, Replace these lines"
        Set TrackChanges = Range(Range(Cells(11, TaskCol), Cells(10000, TaskCol)), _ 
        Range(Cells(11, HoursCol), Cells(10000, HoursCol)), _ 
        Range(Cells(11, DaysCol), Cells(10000, DaysCol))) 
        If Intersect(TrackChanges, Target) Is Nothing Then Exit Sub 
         
        SetColors Target
    With these lines. If you get errorrs, first look for typos. I am famuose for them.
        Set TrackChanges = Range(Range(Cells(11, DaysPeriodCol), Cells(10000, DaysPeriodCol)). _ 
        Range(Cells(11, HoursPeriodCol), Cells(10000, HoursPeriodCol)), _ 
        Range(Cells(11, TimeCompleteCol), Cells(10000, TimeCompleteCol)), _
        Range(Cells(11, DateCompleteCol), Cells(10000, dateCompleteCol))) 
    
        If Intersect(TrackChanges, Target) Is Nothing Then Exit Sub 
        
    Application.EnableEvents = False 
        SetColors Target 
    Application.EnableEvents = True
    These changes means that the procedure will no longer change when you change a task. But then it doesn't need to, does it?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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