Hi all... this forum has been great over the past few days. I'm learning quite a bit.
When the value of the checkbox is true then I would like a linked cell to be filled with a specific color (green) and a different color (red) when the value is false.
In other words, I would like all the affected cells to be red until it becomes a true statement.
Does that make sense?
Let me know if you need a better explanation.
Hi jpcsolutions.
I hate check boxes as they are just another level of complexity to deal with. Why not just put an X in the cell or no X to substitute for a check box. Then you could color the cell based on what was in it.
I've attached an example of what I use instead of check boxes. Simply double click in a cell and it will put in an X or remove it. I like this method much better.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hey MarvinP... This is a great idea.
I have one more thing I'd like to do and I might just run with this...
1. I'd like all the cells in a specific range to be red by default.
2. When a cell under a specific column is double-clicked, how can I make the cell turn green (which in my scenario will indicate the project is completed).
Does that make sense?
This is a much better idea. Thanks.
I actually figured out this one on my own.
First of all, I already have all the cells I need red, colored in red when the spreadsheet opens.
With this code below, the end user will be able to double click a cell, when a specific project is complete and it will turn green. If the end user double clicks it again, it will go back to red.
So, alternating between colors is what this accomplishes. There's a small piece of code at the bottom that allows the color to be changed without actually editing the cell.
I like it!
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Interior.ColorIndex = xlNone Then Target.Interior.ColorIndex = 3 ElseIf Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 4 ElseIf Target.Interior.ColorIndex = 4 Then Target.Interior.ColorIndex = 3 End If Cancel = True End Sub
Great idea with the whole "X" thing... it put me on the track I was looking for.
Thanks,
Jared
Makes perfect sense. You need to look at Conditional Formatting. If there is a blank then make it Red but when there is stuff in it, make it green.
Hope that helps. Study a bit and see if you need more help.
There's one issue I'm running into with the above code and I haven't figured it out yet. There are certain cells that I don't want to turn red when double-clicked. A specific range.
Any ideas how I can isolate a specific group of cells to NOT be affected by the double-click?
-Jared
Hi,
I think I understand the question. If you select the range(s) you want the double clicking to work on and give them a name. Then use this name in your code to only do the event in that range.
Example:
If you have a named range of "WorkHere" (it can be in a lot of different cells, not connected)
Then the code of
Will only change colors in your named range. Is that what you want?If Not Intersect(Target, WorkHere) Is Nothing Then 'Put your coloring code here. End If
Okay. I understand the concept of what you're saying, but I get an error that says...
"Argument Not Optional"
and the following line of code is highlighted...
and here's the full code I have running right now.If Not Intersect(U100workhere) Is Nothing Then
Ideas?Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(U100workhere) Is Nothing Then If Target.Interior.ColorIndex = xlNone Then Target.Interior.ColorIndex = 3 ElseIf Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 4 ElseIf Target.Interior.ColorIndex = 4 Then Target.Interior.ColorIndex = 3 End If End If Cancel = True End Sub
Thanks,
Jared
You need this:
Intersect needs two ranges and you only have one.If Not Intersect(Target, U100workhere) Is Nothing Then
This is the exact code I entered, with the "End If" tag at the bottom. I am still getting a compile error...
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, U100workhere) Is Nothing Then If Target.Interior.ColorIndex = xlNone Then Target.Interior.ColorIndex = 3 ElseIf Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 4 ElseIf Target.Interior.ColorIndex = 4 Then Target.Interior.ColorIndex = 3 End If Cancel = True End If End Sub
Thoughts?
-Jared
Try this,
If Not Intersect(Target, Range("U100workhere")) Is Nothing Then
Hey Marvin...
It was the extra parenthetical citation that did it. Much thanks to you!
Everything works great!
-Jared
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks