Solved If a series of cells in one column are all green, then...

September 8, 2014 at 20:58:28
Specs: Windows 7
Hello everyone! Using Excel 2013. I was wondering how to do the following: I have five cells. When each of them are turned to a specific color of green, I would like to make another cell, elsewhere on the sheet, turn a specific color of green. (The two colors of green would be different, but I doubt that matters).

HALP!


See More: If a series of cells in one column are all green, then...

Report •


✔ Best Answer
September 19, 2014 at 07:17:06
Try this code.

What this does is set the font color of "Not Complete" to match the cell fill color.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        Cancel As Boolean)

' This subroutine toggles the color of cell from Clear to Yellow
' to Green to Clear with each Double-Click. It then counts the
' number of Green cells in a Column and turns the Completed task
' cells Green if all tasks are complete.

'Find current location of template
   With Columns("A")
      Set t = .Find("Template", lookat:=xlWhole)
   End With
'Find Start of last Task Table (Sunday)
   For s_rw = t.Row To 1 Step -1
     If Range("C" & s_rw) = "Sunday" Then
       sunday_rw = s_rw
       Exit For
     End If
   Next
'Find End of last Task Table (Floss)
   For f_rw = sunday_rw To t.Row
     If Range("E" & f_rw) = "Floss" Then
       floss_rw = f_rw
       Exit For
     End If
   Next
   
'Find first of 4 Yellow cells
   For y_rw = floss_rw To t.Row
    If Cells(y_rw, Target.Column) = "Not Complete"  Then
       yellow_rw = y_rw
       Exit For
    End If
   Next

   
'Ensure selected cell is within Latest Task Table
     Select Case Target.Column
        Case 3, 5, 7, 9, 11, 13, 15

     Select Case Target.Row
        Case sunday_rw + 2 To floss_rw
 
  ' If the cell is clear
      If Target.Interior.ColorIndex = xlNone Then
 
  ' Then change the background color to Yellow
         Target.Interior.ColorIndex = 36
 
  ' Else if the cell background color is Yellow
      ElseIf Target.Interior.ColorIndex = 36 Then
 
  ' Then change the background color to Green
         Target.Interior.ColorIndex = 35
 
  ' Else if the cell background color is Green
      ElseIf Target.Interior.ColorIndex = 35 Then
 
  ' Then clear the background
         Target.Interior.ColorIndex = xlNone
 
      End If
 
  ' This is to prevent the cell from being edited when double-clicked
       Cancel = True
    
    
  'Loop through cells in Last Task Table, counting Task Cells and Green Cells
      For Each cell In Range(Cells(sunday_rw + 2, Target.Column), _
                             Cells(floss_rw, Target.Column))
  'Count Task Cells (non-Black Cells)
          If cell.Interior.ColorIndex <> 1 Then task_cell = task_cell + 1
  'Count Green cells
          If cell.Interior.ColorIndex = 35 Then green_cell = green_cell + 1
      Next
  
'If count of Task cells = count of Green cells, then
'Yellow Block and Text Color should be Green
'Otherwise Yellow Block and Text Color should be Yellow
         If task_cell = green_cell Then
             Range(Cells(yellow_rw, Target.Column), _
                   Cells(yellow_rw + 3, Target.Column)).Interior.ColorIndex = 35
             Cells(yellow_rw, Target.Column).Font.ColorIndex = 35
         Else:
             Range(Cells(yellow_rw, Target.Column), _
                   Cells(yellow_rw + 3, Target.Column)).Interior.ColorIndex = 6
             Cells(yellow_rw, Target.Column).Font.ColorIndex = 6
         End If
     
     End Select
     
     End Select
End Sub


Subtle note: There's no need to change "Not Complete" to "All Complete" anymore since it is never seen. In fact, there is no reason to have (specifically) "Not Complete" in the first of the four Yellow cells, but there still has to be something in the cell for the code to search for, so "Not Complete" is as good as anything.

If you decide to change the words in the cell to something else, then you have to change this line to match or the code won't find the cell:

 If Cells(y_rw, Target.Column) = "Not Complete"  Then

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 9, 2014 at 03:07:22
I believe that 2013 lets you check the Conditional Formatting colors, but
since I don't have 2013, I would just add the "other" cell to "Applies To" box of the rules you have for the five cells or just repeat the whole rule for the "other" cell.

MIKE

http://www.skeptic.com/


Report •

#2
September 9, 2014 at 10:35:04
As far as I understand, Conditional Formatting allows you to automatically apply formatting -- like colors -- to cells based on a cell value. But not color (unless cell colors count as a cell value). At least, I can't find a way to change a cell's color based on another cell's color. I was thinking I might have to do it using VB, which I don't know anything about. :)

message edited by dmmagic


Report •

#3
September 9, 2014 at 10:59:53
I want to note that, while looking through other questions in this forum, it seems MiloG, who asked "Changing color of cells based on color of other cells," is looking for exactly the same thing I am. Small world!

It boils down to: There a cell. We want its color to change based on the color of another cell.


Report •

Related Solutions

#4
September 9, 2014 at 12:27:06
I thought there was a way, in Excel 2013 to check the color of a cell, guess I was wrong.

If you can not, in Excel 2013 check the Conditional Formatted color of a cell,
then the only two ways I know of is to:

1) Use the same Formula & Rules that you use to change the "five" cells
to change the color of the "other" cell. The simplest way is to append the
"other" cell to the existing CF in the "Applies To" section.
(This would make so much more sense if I could use Row & Column)

2) Use a Macro, and since my macro skills are just shy of nil, I can't help.

MIKE

http://www.skeptic.com/


Report •

#5
September 10, 2014 at 04:07:55
Could you explain what you mean by "When each of them are turned to a specific color of green"?

How are they being turned green? e.g. Conditional formatting? Manually by the user? A macro?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 10, 2014 at 09:59:51
I have a macro that turns it green. But manually by the user is also fine. However it happens, when a certain cell turns green, I'd like a completely different cell to turn green. Is there a way to attach sheets here? Or I could Dropbox it maybe.

Report •

#7
September 10, 2014 at 12:32:38
Since you have a macro that currently turns the cell green, why not edit the macro to turn the "completely different cell" green at the same time?

To be honest, it's not clear to me what you mean by "But manually by the user is also fine". Do you mean that you would be OK if the user turned the cell green if that was the only way to solve your current problem or do you mean that the cells are currently turned green sometimes by a macro and sometimes manually?

Please keep in mind that we can't see your workbook from where we are sitting nor can we read minds, so we need you to be as detailed as possible when explaining how things are currently being done and what you want to accomplish.

You cannot attach/upload files in this forum, but I guess Dropbox or some other on-line location would work, although some users are reluctant to access files upload by strangers, especially ones that contain macros.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
September 10, 2014 at 12:47:24
I apologize that I wasn't specific enough. Let me try to distill it down. Let's say I have two cells. Both are white. A user manually turns one cell green by using the fill option. I would then like the other cell to turn green automatically.

I do have a macro in play. However, I do not know VB. I got the VB that is in use from a website such as this in order to allow cells to rotate colors when double clicked.

I would be happy to delete the macro and upload a macro-less workbook to Dropbox if my distilled example still isn't clear enough. And thanks to everyone who is assisting. Super appreciative! :)


Report •

#9
September 10, 2014 at 12:56:08
Oh, completely forgot to explain what the workbook is for, in case that helps. Each day, I have a series of tasks I want to compete. (Write, exercise, practice piano, etc). As I complete each task, I double click on the cell and it turns green (via a macro that I found on a site such as this).

At the bottom of this column of "task cells" is a blank cell. When I complete all my tasks in a given day, I manually make it green. If I missed a task, I leave it white. This way I can see how many "green days" were in a given week. I'd love for the blank cell to turn green when all the tasks are completed. Tasks are completed when they are green (via the double-clicking macro).

Hope the extra info helps!


Report •

#10
September 10, 2014 at 19:09:30
OK, now we're getting somewhere. Since you already have a macro that you use to color the cells, it seems to me that the easiest solution is to have the same macro count the number of cells in the column that are colored after each run. Once the correct number of cells are colored, the "Completed" cell can then be colored.

If all of the columns have the same number of tasks, the solution is easier than if each column has a different number, but either way it can be done.

We still need some more details, so here are the next steps.

1 - Delete the code from the workbook and store the workbook on Dropbox or a similar site.
2 - Post a link to the workbook in this thread.
3 - Click on the blue line at the end of this post and read the instructions on how to use the pre tags to post VBA code.
4 - Post the macro in this thread.

Once we safely have the workbook and the code, we should be able to modify the code to count the colored cells and change the color of the "Completed" cell as requested.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
September 10, 2014 at 19:21:37
Wow, very awesome! Thanks so much! I'll get to this tonight at work or tomorrow during the day!

Report •

#12
September 10, 2014 at 20:16:35
Workbook:

https://www.dropbox.com/s/a05xidwv5...

Macro:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors
 
    ' If the cell is clear
    If Target.Interior.ColorIndex = xlNone Then
 
        ' Then change the background color to red
        Target.Interior.ColorIndex = 36
 
    ' Else if the cell background color is red
    ElseIf Target.Interior.ColorIndex = 36 Then
 
        ' Then clear the background
        Target.Interior.ColorIndex = 35
 
    ' Else if the cell background color is red
    ElseIf Target.Interior.ColorIndex = 35 Then
 
        ' Then clear the background
        Target.Interior.ColorIndex = xlNone
 
    End If
 
    ' This is to prevent the cell from being edited when double-clicked
    Cancel = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


Report •

#13
September 11, 2014 at 13:03:53
There are a few things that are still unclear, both in the macro and in your explanation.

1 - The comments in the code don’t match up with the instructions being executed. For example, one comment says “change the background color to red“, but the instruction that follows changes the cell to Yellow. Another says to clear the background, but the instruction that follows changes the cell to Green. Other comments say that the code is checking the cell for Red, when the instruction is actually checking for Yellow or Green. What’s up with all the comments that don’t match the instructions?

2 – You posted a SelectionChange macro with no instructions in it. What is the purpose of that? If it is just hanging around in the sheet module then you should delete it. Even though there are no instructions it in, Excel is going to attempt to run it each and every time you select a cell in that sheet. That’s a waste of resources.

3 – You didn’t say what cell you want to turn Green when all tasks are completed (i.e. Green). For the purposes of this exercise, I am going to use the cell in Row 43.

That said, try the code that I have posted below. How the code works is as follows:

1 - Each time a cell within the Tasks Table is double-clicked, the first part of the code will toggle the fill color from None to Yellow to Green to None.

2 – The second part of the code will count all of the Task Cells in the Target column (those cells that are not Black) and also count all cells that are Green in that same column.

3 - If the count of Task Cells equals the count of Green Cells, then all Tasks are marked as complete. If that is the case, then the cell in Row 43 will read “All Complete” and become green. If not, the cell in Row 43 will read “Not Complete” and the color will be cleared. The cell in Row 43 will change to Not Complete and the color will be cleared if you "uncomplete" one or more Tasks that were previously Green. This allows for changes if you make a mistake or decide afterwards that a Task is not really complete.

One other note: In the original code, double clicking any cell - even if it was outside of the Tasks Table - would toggle the color of the cell. I have added an instruction to only have the colors changed within the Tasks Table itself.

Let me know how it works for you.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' This subroutine toggles the color of cell from Clear to Yellow to Green to Clear
' with each Double-Click. It then counts the number of Green cells in a Column and
' turns the Completed task cell Green if all tasks are complete.

  'Ensure selected cell is within Task Table
     Select Case Target.Column
        Case 3, 5, 7, 9, 11, 13, 15

     Select Case Target.Row
        Case 4 To 34
 
  ' If the cell is clear
      If Target.Interior.ColorIndex = xlNone Then
 
  ' Then change the background color to Yellow
         Target.Interior.ColorIndex = 36
 
  ' Else if the cell background color is Yellow
      ElseIf Target.Interior.ColorIndex = 36 Then
 
  ' Then change the background color to Green
         Target.Interior.ColorIndex = 35
 
  ' Else if the cell background color is Green
      ElseIf Target.Interior.ColorIndex = 35 Then
 
  ' Then clear the background
         Target.Interior.ColorIndex = xlNone
 
      End If
 
  ' This is to prevent the cell from being edited when double-clicked
       Cancel = True
    
    
  'Loop through cells in Rows 4 - 34)
       For Each cell In Range(Cells(4, Target.Column), Cells(34, Target.Column))
  'Count Task Cells (non-Black Cells)
          If cell.Interior.ColorIndex <> 1 Then task_cell = task_cell + 1
  'Count Green cells
          If cell.Interior.ColorIndex = 35 Then green_cell = green_cell + 1
       Next
  'If count of Task cells = count of Green cells, then Row 43 cell should be green
  'Row 43 cell should be Clear otherwise
         If task_cell = green_cell Then
             Cells(43, Target.Column).Interior.ColorIndex = 35
             Cells(43, Target.Column) = "All Complete"
         Else:
             Cells(43, Target.Column).Interior.ColorIndex = xlNone
             Cells(43, Target.Column) = "Not Complete"
         End If
     End Select
     End Select
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
September 11, 2014 at 13:41:51
It works perfectly! To answer your questions:

1) The reason the comments code does not match up perfectly is because I copied the code from a how-to site. I made changes to the code, and did not think to edit the comments. That's my bad.

2) Unfortunately, without more knowledge of VB, I don't quite understand what the SelectionChange is for. With the exception of making changes so the colors suited my needs, I did not change the macro from where I copied it from. Here's where I copied it from: http://theclosetentrepreneur.com/ho...

3) Very true -- my apologies for not stating that. It was my intention for the three yellow cells beneath each column to turn green.

Now here's the thing: You will note, at the top of each column is a date. When the week is over, I copy a blank week and paste it beneath the completed week. I then change the dates to reflect the upcoming week. Currently, the code only works on specific rows, so newly pasted weeks are not affected by the code you provided.

Here's a new link to a file in order to boost my description of what I mean: https://www.dropbox.com/s/8xmlaxrdz...

You will note I have the next several weeks set up, with a template at the bottom. It is from this template that I copy and insert new weeks.

Also, I want to say thanks again! You're being an amazing help!


Report •

#15
September 11, 2014 at 17:23:01
I will work on the code but first I have to make a comment. I only bring this up so that when you look for help in the future, whether it's in this forum or another, it will be easier for all involved.

In order for anyone to help you, you have to provide as much accurate detail as possible so that those that want to help have the information they need.

As just one example, in an earlier post you said: "It boils down to: There's a cell. We want its color to change based on the color of another cell."

Based on your use of "a" and "its", it appears to us that you were talking about a single cell. Now you tell us " It was my intention for the three yellow cells beneath each column to turn green." So, is it 1 cell or 3? Oh...wait...when I look at the spreadsheet, I see four yellow cells beneath each column. So is it 1, 3 or 4 cells? I'll guess 4...but I shouldn't have to guess.

In addition, in your earlier posts, you made it sound like it was a single cell that the color change was based on, when in reality you want a set of cells to change color but only after another set of cells have all been changed to green.

I'm sure you see my point. Since we can only offer solutions based on what you tell us, we spend time working with what you tell us, only to have to rework it when you add more detail. We shouldn't have to drag the details out of you in a series of back and forth posts. Please keep this in mind when posting questions in the future. Thanks!

Anyway...back to work on the macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
September 11, 2014 at 17:26:58
I have to apologize again for not being more clear. I see now that I was not nearly specific enough, and it cost you your time. I will certainly use this as a learning experience. And must stress how much I appreciate your help: help that comes at your leisure and free of charge. Very awesome of you.

Edit: You are correct: There are four cells under each column. That is my mistake; I miscounted. I appreciate you being so thorough. :)

message edited by dmmagic


Report •

#17
September 11, 2014 at 18:59:51
In your latest example workbook, there are 2 sheets. Each sheet has a different number of rows in the Task Table. Are you going to have multiple tables, all with a differing number of rows?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
September 11, 2014 at 19:09:17
That extra sheet (Called "Janelle's Schedule") was meant to be deleted. I forgot to do this for the second upload. My apologies.

Report •

#19
September 11, 2014 at 19:35:05
So, are you saying that the code only has to work for the single table, which will have a consistent number of rows?

The reason I ask is that coding for a specific number of Rows, e.g. 31, is easier than having to have the code figure out where the table begins and ends. On the other hand, if you ever add or delete rows (i.e. Tasks) the code may not work if it is hard coded for a specific number of Rows.

So, do you see yourself ever needing the code to work on tables of varying lengths?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#20
September 11, 2014 at 19:42:25
A very good question. Upon thinking about it, yes -- I see a situation where I might add or remove rows in the future. Thank you for asking. I hadn't thought of that. :)

Report •

#21
September 12, 2014 at 06:27:35
This is going to take some time. A variable number of task rows as well as the continued insertion of new tables makes complete automation more difficult. There may have to be some text or something added to the table so that the code can locate the last entry in the task list. For example, the text Sunday can be used to find the beginning of the table, but at this point I don't see anything specific that denotes the bottom of the table. We may need to add a row at the bottom of the Task Table with something that allows the code to find the bottom.

In addition, if the number of grey cells at the bottom of the table changes, the yellow "Complete" cells won't be where the code expects them to be.

I'll try to find time to work on this over the weekend.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#22
September 12, 2014 at 11:07:30
If every day had a cell at the bottom with text that never changed -- for example, if I moved "floss" to the bottom row so that it was the last cell in every day, would that make a difference? Then new rows, if ever added, would be added above "floss"?

Report •

#23
September 12, 2014 at 12:13:07
That should help as long as you remember that Floss has to always be in the last row. I'll try that the next time I get back to coding.

The other issue is the location of the 4 Yellow cells that we want to turn Green. Even though I was not supposed to see "Janelle's Schedule", I did see it and if I recall correctly there are less Grey cells under the Task Table than in the "Chris' Schedule" sheet. That means I can't simply have the code use Floss_Row + 10 to get to the first of the four Yellow cells. In other words, in the "Chris' Schedule" sheet, the first Yellow cell is 10 rows below the end of the Task Table. As long as the code can determine what row "Floss" is in, it can add 10 to that to get to the first Yellow cell. However, if the Yellow cells might be 8, 9 ,11, etc. rows below the Task Table, there would need to be something that the code could use to determine their location. Something like a specific text in the first Yellow cell, etc. I suppose if I put some Yellow text in the first Yellow cell in the template, then it would be there for the code to find but wouldn't be seen by the user. I'll play with that idea also.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#24
September 12, 2014 at 12:30:09
Janelle (my wife) has no interest in making her daily routine sheet work cool like mine. In fact, she never uses it. I've been trying to get her in on it, because I'd be lost without mine. But she says it just doesn't work for her. Oh well. :)

Report •

#25
September 12, 2014 at 12:34:29
Don't push her too far or you'll be lost without her also. :-(


message edited by DerbyDad03


Report •

#26
September 15, 2014 at 08:35:03
Just FYI...I didn't get a chance to work on this at all this weekend. I'll get back to it this week.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#27
September 15, 2014 at 10:57:44
No problem at all! Thanks for even taking the time to look into it! :)

Report •

#28
September 16, 2014 at 23:53:20
OK, give this code a try, after making the following changes to your Template:

1 - The last task of each day must be Floss
2 - The first cell in each Yellow block at the bottom of the daily tasks must read Not Complete

Note 1: It appears that you do not use this worksheet on Saturdays or Sundays. If you decide to add those days, be sure to add a Yellow block with the words Not Complete in your template.

Note 2: You must run the code after inserting the new template in order for it to work. It will not work on the existing Task Tables.

Note 3: I won't be surprised if the code throws up an error since I am not using it in the exact same workbook as you. We'll deal with that if it happens.

The way the code works is as follows:

At each double-click:

1 - The code searches for the word Template in Column A.
2 - Once Template is found it searches backwards to find the word Sunday in Column C. It now knows the first Row of the latest Task Table.
3 - Once the word Sunday is found, it searches forward to find the word Floss in Column E. It now knows the last Row of the latest Task Table.
4 - The code continues to search forward until it finds the words Not Complete or All Complete. It now knows the location of the 4 yellow cells below each daily Task List.
5 - Once it knows all of this information about the latest Task Table, it is able to make the color changes and count the Green cells only within the latest Table.

Good luck!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        Cancel As Boolean)

' This subroutine toggles the color of cell from Clear to Yellow
' to Green to Clear with each Double-Click. It then counts the
' number of Green cells in a Column and turns the Completed task
' cells Green if all tasks are complete.

'Find current location of template
   With Columns("A")
      Set t = .Find("Template", lookat:=xlWhole)
   End With
'Find Start of last Task Table (Sunday)
   For s_rw = t.Row To 1 Step -1
     If Range("C" & s_rw) = "Sunday" Then
       sunday_rw = s_rw
       Exit For
     End If
   Next
'Find End of last Task Table (Floss)
   For f_rw = sunday_rw To t.Row
     If Range("E" & f_rw) = "Floss" Then
       floss_rw = f_rw
       Exit For
     End If
   Next
   
'Find first of 4 Yellow cells
   For y_rw = floss_rw To t.Row
    If Cells(y_rw, Target.Column) = "Not Complete" Or _
       Cells(y_rw, Target.Column) = "All Complete" Then
      yellow_rw = y_rw
      Exit For
    End If
   Next

   
'Ensure selected cell is within Latest Task Table
     Select Case Target.Column
        Case 3, 5, 7, 9, 11, 13, 15

     Select Case Target.Row
        Case sunday_rw + 2 To floss_rw
 
  ' If the cell is clear
      If Target.Interior.ColorIndex = xlNone Then
 
  ' Then change the background color to Yellow
         Target.Interior.ColorIndex = 36
 
  ' Else if the cell background color is Yellow
      ElseIf Target.Interior.ColorIndex = 36 Then
 
  ' Then change the background color to Green
         Target.Interior.ColorIndex = 35
 
  ' Else if the cell background color is Green
      ElseIf Target.Interior.ColorIndex = 35 Then
 
  ' Then clear the background
         Target.Interior.ColorIndex = xlNone
 
      End If
 
  ' This is to prevent the cell from being edited when double-clicked
       Cancel = True
    
    
  'Loop through cells in Last Task Table, counting Task Cells and Green Cells
      For Each cell In Range(Cells(sunday_rw + 2, Target.Column), _
                             Cells(floss_rw, Target.Column))
  'Count Task Cells (non-Black Cells)
          If cell.Interior.ColorIndex <> 1 Then task_cell = task_cell + 1
  'Count Green cells
          If cell.Interior.ColorIndex = 35 Then green_cell = green_cell + 1
      Next
  
  'If count of Task cells = count of Green cells, then
  'Yellow Block should be Green, Text should read "All Complete"
  'Otherwise Yellow Block should be Yellow, Text should read "Not Complete"
         If task_cell = green_cell Then
             Range(Cells(yellow_rw, Target.Column), _
                   Cells(yellow_rw + 3, Target.Column)).Interior.ColorIndex = 35
             Cells(yellow_rw, Target.Column) = "All Complete"
         Else:
             Range(Cells(yellow_rw, Target.Column), _
                   Cells(yellow_rw + 3, Target.Column)).Interior.ColorIndex = 6
             Cells(yellow_rw, Target.Column) = "Not Complete"
         End If
     
     End Select
     
     End Select
End Sub


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#29
September 18, 2014 at 02:09:55
It says "Run-time error 91: Object variable or With block variable not set."

Report •

#30
September 18, 2014 at 03:57:13
Put a copy of the updated workbook on Dropbox. You can leave the macro in it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#31
September 18, 2014 at 10:23:09
I just did a little playing around and was able to reproduce that error by not having the string Template in Column A. That's the only part of the code that uses the With method, so I assume your error is related to that.

As written, the code is looking for the string Template, with no extra spaces or hidden characters in the cell. Either ensure that the cell contains only Template or change the instruction to read:

Set t = .Find("Template", lookat:=xlPart)

By doing a "partial" search of the cell, any extra spaces won't bother the code as long as the string Template is somewhere in the cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#32
September 18, 2014 at 15:16:04
It works! No changes to the code were necessary -- I'd accidentally left off the template, and since word "Template" wasn't present in the sheet, the code went haywire. I really appreciate the amount of work you put into this! It does exactly what I want.

Now, if I may pick your brain for one other small issue: SInce the code is dependant on there being the words "Not Complete" in the first yellow cell under each day, I colored the text yellow so that it is not seen. But when the four yellow cells turn green, the yellow text is suddenly visible. Any way to change the text green when the cells turn green?

Again, I cannot thank you enough. Very very awesome -- and I hope this thread serves to help others!


Report •

#33
September 19, 2014 at 07:17:06
✔ Best Answer
Try this code.

What this does is set the font color of "Not Complete" to match the cell fill color.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        Cancel As Boolean)

' This subroutine toggles the color of cell from Clear to Yellow
' to Green to Clear with each Double-Click. It then counts the
' number of Green cells in a Column and turns the Completed task
' cells Green if all tasks are complete.

'Find current location of template
   With Columns("A")
      Set t = .Find("Template", lookat:=xlWhole)
   End With
'Find Start of last Task Table (Sunday)
   For s_rw = t.Row To 1 Step -1
     If Range("C" & s_rw) = "Sunday" Then
       sunday_rw = s_rw
       Exit For
     End If
   Next
'Find End of last Task Table (Floss)
   For f_rw = sunday_rw To t.Row
     If Range("E" & f_rw) = "Floss" Then
       floss_rw = f_rw
       Exit For
     End If
   Next
   
'Find first of 4 Yellow cells
   For y_rw = floss_rw To t.Row
    If Cells(y_rw, Target.Column) = "Not Complete"  Then
       yellow_rw = y_rw
       Exit For
    End If
   Next

   
'Ensure selected cell is within Latest Task Table
     Select Case Target.Column
        Case 3, 5, 7, 9, 11, 13, 15

     Select Case Target.Row
        Case sunday_rw + 2 To floss_rw
 
  ' If the cell is clear
      If Target.Interior.ColorIndex = xlNone Then
 
  ' Then change the background color to Yellow
         Target.Interior.ColorIndex = 36
 
  ' Else if the cell background color is Yellow
      ElseIf Target.Interior.ColorIndex = 36 Then
 
  ' Then change the background color to Green
         Target.Interior.ColorIndex = 35
 
  ' Else if the cell background color is Green
      ElseIf Target.Interior.ColorIndex = 35 Then
 
  ' Then clear the background
         Target.Interior.ColorIndex = xlNone
 
      End If
 
  ' This is to prevent the cell from being edited when double-clicked
       Cancel = True
    
    
  'Loop through cells in Last Task Table, counting Task Cells and Green Cells
      For Each cell In Range(Cells(sunday_rw + 2, Target.Column), _
                             Cells(floss_rw, Target.Column))
  'Count Task Cells (non-Black Cells)
          If cell.Interior.ColorIndex <> 1 Then task_cell = task_cell + 1
  'Count Green cells
          If cell.Interior.ColorIndex = 35 Then green_cell = green_cell + 1
      Next
  
'If count of Task cells = count of Green cells, then
'Yellow Block and Text Color should be Green
'Otherwise Yellow Block and Text Color should be Yellow
         If task_cell = green_cell Then
             Range(Cells(yellow_rw, Target.Column), _
                   Cells(yellow_rw + 3, Target.Column)).Interior.ColorIndex = 35
             Cells(yellow_rw, Target.Column).Font.ColorIndex = 35
         Else:
             Range(Cells(yellow_rw, Target.Column), _
                   Cells(yellow_rw + 3, Target.Column)).Interior.ColorIndex = 6
             Cells(yellow_rw, Target.Column).Font.ColorIndex = 6
         End If
     
     End Select
     
     End Select
End Sub


Subtle note: There's no need to change "Not Complete" to "All Complete" anymore since it is never seen. In fact, there is no reason to have (specifically) "Not Complete" in the first of the four Yellow cells, but there still has to be something in the cell for the code to search for, so "Not Complete" is as good as anything.

If you decide to change the words in the cell to something else, then you have to change this line to match or the code won't find the cell:

 If Cells(y_rw, Target.Column) = "Not Complete"  Then

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#34
September 23, 2014 at 18:42:55
That's it -- it worked! Very very awesome! Thanks so much!

Report •

#35
September 23, 2014 at 19:12:19
Since we're already in this deep, I was wondering about something else. Let's say I decide to "set up" the next four weeks. The first three weeks don't work using this code; double-clicking on any of their cells makes it so that the blinking cursor is placed in the cell for text editing, as normal. Only the week immediately preceding the template (in this example, the fourth week) works as normal. What would be needed to make it so any week\ in the workbook can make use of this code?

Report •

#36
September 24, 2014 at 07:15:31
Don't take this the wrong way, but I've already put too many hours into this project between trying to get clarity about what you are trying to do, the changes you've requested already and then the actual coding itself. Your latest request is not trivial and will require more time and effort than I am willing to expend on it. Maybe someone else will step up and offer some code that meets your latest requirements.

There are two major issues with additional requests after a solution that meets the original requirements has been presented:

1 - We either end up with inefficient, bloated code as we bolt on sections to meet the new requirements; or
2 - We have to completely re-write the code, essentially throwing away all of the previous work.

That said, I have a suggestion:

1 - Add "Practice VBA" to your daily Task list.

2 - Refer to this tutorial on Debugging VBA Code

http://www.computing.net/howtos/sho...

3 - Use the code I've included below to familiarize yourself with the debugging techniques. The code includes some of the same instructions that your Task sheet code uses.

4 - Once you are familiar with using the debugging techniques on the relatively simple code shown below, you can try them on the Task list macro. Once you understand how that macro works, you can modify it yourself.

The main way that I learned to write VBA code was via trial and error and by using the debugging techniques in the tutorial mentioned above. In fact, after using the techniques for many years, I wrote the tutorial in an effort to help others learn to write their own code. At a minimum the techniques in the tutorial can help the reader understand the macros they find in forums such as this one so that they can modify them for their own needs.

If you have any specific questions related to VBA coding in general, feel free to start a thread with your questions and we'll try to help.

Good luck!

Sub Practice_Code()
Dim rw As Integer
Dim c As Range

'Clear Range contents and fill color before each run
    Sheets(1).Range("A1:A10").ClearContents
    Sheets(1).Range("A1:A10").Interior.ColorIndex = xlNone
    
'Add values to Range
    For rw = 1 To 6
     Sheets(1).Range("A" & rw) = rw * 2
    Next
    
'Find the value 4 within the Range
'Set the cell color to Blue
    With Sheets(1).Range("A1:A6")
      Set c = .Find(8, lookat:=xlWhole)
        If Not c Is Nothing Then
         Sheets(1).Range("A" & c.Row).Interior.ColorIndex = 4
'Move up 2 rows and color the cell Yellow
         Sheets(1).Range("A" & c.Row - 2).Interior.ColorIndex = 6
        End If
    End With
    
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#37
September 26, 2014 at 00:00:22
I understand and I appreciate the help you have given.

Report •

Ask Question