Here is a real challenge!

J

jwleonard

I am looking for a way to define a range based on the background colo
of cells. I need to name a range and have it consist of all cells wit
a white background. I have been looking on this forum for hours tryin
to find some code to do this, all I can find is stuff to count, sum
and stuff like that. I tried to use these and modify them but I reall
don't understand VBA so it didn't work (although I did get close)!
would really appreciate some help on this, Thanks
 
W

William

Hi

Try...


Sub RangeBasedUponColor()
Dim r As Range, c As Range, rr As Range
Set r = ActiveSheet.UsedRange
For Each c In r
If c.Interior.ColorIndex = 2 Then
If rr Is Nothing Then
Set rr = c
Else
Set rr = Union(rr, c)
End If
End If
Next c
If Not rr Is Nothing Then
With ActiveSheet
ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
..Range("Background").Select
End With
End If
End Sub


--
XL2002
Regards

William

[email protected]

| I am looking for a way to define a range based on the background color
| of cells. I need to name a range and have it consist of all cells with
| a white background. I have been looking on this forum for hours trying
| to find some code to do this, all I can find is stuff to count, sum,
| and stuff like that. I tried to use these and modify them but I really
| don't understand VBA so it didn't work (although I did get close)! I
| would really appreciate some help on this, Thanks.
|
|
| ---
|
|
 
J

jwleonard

Thanks for your quick response!!! That is almost it... First I had t
edit it to make it work, look below at the line in red; I had to chang
it to: Range("Background").Select (I removed the two periods).

Sub RangeBasedUponColor()
Dim r As Range, c As Range, rr As Range
Set r = ActiveSheet.UsedRange
For Each c In r
If c.Interior.ColorIndex = 2 Then
If rr Is Nothing Then
Set rr = c
Else
Set rr = Union(rr, c)
End If
End If
Next c
If Not rr Is Nothing Then
With ActiveSheet
ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
..Range("Background").Select
End With
End If
End Sub

I kept getting an error until I did that. Now it works but only if
run it like a macro, is it possible to have it run so if a change i
made it will catch it without having to run the macro again? If tha
is not possible then I guess I could just have the macro run everytim
the file is opened.

Second, is there a way to keep from selecting the cells, I just want i
to define a name not really select the cells.

Thanks again for your help and quick reply
 
W

William

Hi

In my original post there was only one period - must be something to do with
your newsreader, etc. You should really keep the one period although in this
particular sutuation I do not think it really matters. Either way the line "
..Range("Background").Select " is uneccessary and I included it only for you
to see the cells that had been included within the name "Background".

--
XL2002
Regards

William

[email protected]

| Thanks for your quick response!!! That is almost it... First I had to
| edit it to make it work, look below at the line in red; I had to change
| it to: Range("Background").Select (I removed the two periods).
|
| Sub RangeBasedUponColor()
| Dim r As Range, c As Range, rr As Range
| Set r = ActiveSheet.UsedRange
| For Each c In r
| If c.Interior.ColorIndex = 2 Then
| If rr Is Nothing Then
| Set rr = c
| Else
| Set rr = Union(rr, c)
| End If
| End If
| Next c
| If Not rr Is Nothing Then
| With ActiveSheet
| ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
| .Range("Background").Select
| End With
| End If
| End Sub
|
| I kept getting an error until I did that. Now it works but only if I
| run it like a macro, is it possible to have it run so if a change is
| made it will catch it without having to run the macro again? If that
| is not possible then I guess I could just have the macro run everytime
| the file is opened.
|
| Second, is there a way to keep from selecting the cells, I just want it
| to define a name not really select the cells.
|
| Thanks again for your help and quick reply.
|
|
| ---
|
|
 
W

William

Here is the revised code so that the range is NOT selected.

Sub RangeBasedUponColor()
Dim r As Range, c As Range, rr As Range
Set r = ActiveSheet.UsedRange
For Each c In r
If c.Interior.ColorIndex = 2 Then
If rr Is Nothing Then
Set rr = c
Else
Set rr = Union(rr, c)
End If
End If
Next c
If Not rr Is Nothing Then
With ActiveSheet
ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
End With
End If
End Sub

--
XL2002
Regards

William

[email protected]

| Thanks for your quick response!!! That is almost it... First I had to
| edit it to make it work, look below at the line in red; I had to change
| it to: Range("Background").Select (I removed the two periods).
|
| Sub RangeBasedUponColor()
| Dim r As Range, c As Range, rr As Range
| Set r = ActiveSheet.UsedRange
| For Each c In r
| If c.Interior.ColorIndex = 2 Then
| If rr Is Nothing Then
| Set rr = c
| Else
| Set rr = Union(rr, c)
| End If
| End If
| Next c
| If Not rr Is Nothing Then
| With ActiveSheet
| ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
| .Range("Background").Select
| End With
| End If
| End Sub
|
| I kept getting an error until I did that. Now it works but only if I
| run it like a macro, is it possible to have it run so if a change is
| made it will catch it without having to run the macro again? If that
| is not possible then I guess I could just have the macro run everytime
| the file is opened.
|
| Second, is there a way to keep from selecting the cells, I just want it
| to define a name not really select the cells.
|
| Thanks again for your help and quick reply.
|
|
| ---
|
|
 
W

William

Too much coffee this morning......

To avoid running a macro manually each time a change is made to a cell's
colour, the code below will execute each time you select a new cell.
Depending upon the size of your workbook, this may impair performance.

Save the code to the relevant "Worksheet" module, not a general module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, c As Range, rr As Range
'Amend sheet name as necessary
Set r = Sheets("Sheet1").UsedRange
Application.Calculation = xlCalculationManual
For Each c In r
If c.Interior.ColorIndex = 2 Then
If rr Is Nothing Then
Set rr = c
Else
Set rr = Union(rr, c)
End If
End If
Next c
If Not rr Is Nothing Then
'Amend sheet name as necessary
With Sheets("Sheet1")
ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
End With
End If
Application.Calculation = xlCalculationAutomatic
End Sub


--
XL2002
Regards

William

[email protected]

| Here is the revised code so that the range is NOT selected.
|
| Sub RangeBasedUponColor()
| Dim r As Range, c As Range, rr As Range
| Set r = ActiveSheet.UsedRange
| For Each c In r
| If c.Interior.ColorIndex = 2 Then
| If rr Is Nothing Then
| Set rr = c
| Else
| Set rr = Union(rr, c)
| End If
| End If
| Next c
| If Not rr Is Nothing Then
| With ActiveSheet
| ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
| End With
| End If
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
| [email protected]
|
| | | Thanks for your quick response!!! That is almost it... First I had to
| | edit it to make it work, look below at the line in red; I had to change
| | it to: Range("Background").Select (I removed the two periods).
| |
| | Sub RangeBasedUponColor()
| | Dim r As Range, c As Range, rr As Range
| | Set r = ActiveSheet.UsedRange
| | For Each c In r
| | If c.Interior.ColorIndex = 2 Then
| | If rr Is Nothing Then
| | Set rr = c
| | Else
| | Set rr = Union(rr, c)
| | End If
| | End If
| | Next c
| | If Not rr Is Nothing Then
| | With ActiveSheet
| | ThisWorkbook.Names.Add Name:="Background", RefersTo:=rr
| | .Range("Background").Select
| | End With
| | End If
| | End Sub
| |
| | I kept getting an error until I did that. Now it works but only if I
| | run it like a macro, is it possible to have it run so if a change is
| | made it will catch it without having to run the macro again? If that
| | is not possible then I guess I could just have the macro run everytime
| | the file is opened.
| |
| | Second, is there a way to keep from selecting the cells, I just want it
| | to define a name not really select the cells.
| |
| | Thanks again for your help and quick reply.
| |
| |
| | ---
| |
| |
|
|
 
J

jwleonard

Thanks for all the help! I was able to get it to do what I asked for
but I couldn't do what I wanted with it.
If you still fell like helping me I need to find a way to define
range in the "Allow users to edit ranges" that will update based o
those background colors.
When I originally started this post I needed to allow users to edi
cells with white or yellow backgrounds and not others.
So I asked for a way to name a range based on the background color
once I had that I put the name of the range in the "refers to cells
block of "Allow users to edit ranges". I didn’t know that once I pu
the named range in the block and hit O.K. it would convert to the cel
references from that range. The problem is that it won't keep up wit
updates, if I add rows and make some cells white or yellow then othe
users are not able to update them because while the rangebaseduponcolo
picks up the change when the macro runs again the cell references in th
"Allow users to edit ranges" remains the same. I hope that makes sense
and someone can help me out!
William, I apologize for wasting your time before, I should have jus
asked for what I needed instead of thinking I had it all figured out.
Thanks again
 
W

William

Hi

I'm having difficulty in understanding exactly what you want to achieve...
1) Can Users change the colour of any cell thus unprotecting that cell for
editing? If so what is the point of protecting the sheet.
2) Can Users insert / delete columns / rows? If so how do you see this being
dealt with.

Are you sure you really need "Allow users to edit ranges" or are you just
looking for code to unlock those cells shaded white so users can enter data
into those cells.

Sorry I can't help at this stage but I do not understand your ultimate goal.
IE What exactly are the Users allowed to do, who decides and changes the
background colours of cells, etc. If you post back with some more info, I'll
be glad to help if I can.

--
XL2002
Regards

William

[email protected]

| Thanks for all the help! I was able to get it to do what I asked for,
| but I couldn't do what I wanted with it.
| If you still fell like helping me I need to find a way to define a
| range in the "Allow users to edit ranges" that will update based on
| those background colors.
| When I originally started this post I needed to allow users to edit
| cells with white or yellow backgrounds and not others.
| So I asked for a way to name a range based on the background color,
| once I had that I put the name of the range in the "refers to cells"
| block of "Allow users to edit ranges". I didn’t know that once I put
| the named range in the block and hit O.K. it would convert to the cell
| references from that range. The problem is that it won't keep up with
| updates, if I add rows and make some cells white or yellow then other
| users are not able to update them because while the rangebaseduponcolor
| picks up the change when the macro runs again the cell references in the
| "Allow users to edit ranges" remains the same. I hope that makes sense,
| and someone can help me out!
| William, I apologize for wasting your time before, I should have just
| asked for what I needed instead of thinking I had it all figured out.
| Thanks again!
|
|
| ---
|
|
 
J

jwleonard

I was afraid you were going to say that!!! Here it goes...
1) Users can't change the background colors of the cells, only I can.
When I protect I only allow select locked and unlocked cells.
2) users can't insert/delete columns/rows.

The ultimate goal is...
I have a workbook with multiple worksheets (one for each group o
multiple users). I need to allow them to input data into cells wit
white and yellow background ONLY and ONLY on their groups worksheet.
So I need to use allow users to edit ranges to control which users ca
edit which worksheets. I am already at this point. The problem come
in when I must add rows to and input new data (all worksheets ge
updated at the same time with new rows and data). Since the "allo
users to edit ranges" converts my named range to a list of cell range
after I click O.K., my new rows are not seen in "allow users to edi
ranges", so I would need to go to each worksheet and update the range
that users can edit everytime I add new rows (don't have time to d
this).

I originally had white and yellow cells unlocked, then when I protecte
the sheet I only allowed select unlocked cells. This worked great unti
my boss told me we need to keep users in one group from editing anothe
groups worksheet. If I allow users to select all cells but only edi
the ones with white or yellow backgrounds then I can control who ca
edit on a worksheet by the list of users that can edit without
password.

If you would like I can send you the file I am working on so you ca
see what I have. I don't want to post it on the web though. Just le
me know.

I hope this explains what I am trying to do, if you need more just le
me know. You really have no idea how much I appreciate your help o
this
 
W

William

Hi

Best to email me the file - please make sure that the word "Excel" is
included within the subject line of the email or my spam blocker will
automatically delete it. Thanks.

--
XL2002
Regards

William

[email protected]

| I was afraid you were going to say that!!! Here it goes...
| 1) Users can't change the background colors of the cells, only I can.
| When I protect I only allow select locked and unlocked cells.
| 2) users can't insert/delete columns/rows.
|
| The ultimate goal is...
| I have a workbook with multiple worksheets (one for each group of
| multiple users). I need to allow them to input data into cells with
| white and yellow background ONLY and ONLY on their groups worksheet.
| So I need to use allow users to edit ranges to control which users can
| edit which worksheets. I am already at this point. The problem comes
| in when I must add rows to and input new data (all worksheets get
| updated at the same time with new rows and data). Since the "allow
| users to edit ranges" converts my named range to a list of cell ranges
| after I click O.K., my new rows are not seen in "allow users to edit
| ranges", so I would need to go to each worksheet and update the ranges
| that users can edit everytime I add new rows (don't have time to do
| this).
|
| I originally had white and yellow cells unlocked, then when I protected
| the sheet I only allowed select unlocked cells. This worked great until
| my boss told me we need to keep users in one group from editing another
| groups worksheet. If I allow users to select all cells but only edit
| the ones with white or yellow backgrounds then I can control who can
| edit on a worksheet by the list of users that can edit without a
| password.
|
| If you would like I can send you the file I am working on so you can
| see what I have. I don't want to post it on the web though. Just let
| me know.
|
| I hope this explains what I am trying to do, if you need more just let
| me know. You really have no idea how much I appreciate your help on
| this!
|
|
| ---
|
|
 
Top