PDA

View Full Version : Clearer example



ozibryan
01-09-2006, 04:52 AM
I am sure this is a simple thing to do once you know how, however - by now, I resemble one of those cartoon characters that digs a very deep hole going in circles.

The attached example (JJacob) -has an initial worksheet (Simple) that has data validations applied to column A. These values are 1, 2, 3. In this example, if A1 selects value 1 then I'd want to shade C1 thru H1 light brown, so on and so on.

The subsequent worksheet (PF6) shows a suggested example sourced from JJacob (via RoyUK) which demonstrates how to use the values of a select statement to perfom various actions.

What I want to do is to adapt the JJacob sheet which uses a 'fixed' range of 'E37' to work for all rows in a column when selected, as per the simple example.

Any suggestions are really really welcome.

:hi:

Bob Phillips
01-09-2006, 05:27 AM
No workbook.

ozibryan
01-09-2006, 01:12 PM
Whoops.. , please see workbook attached.

mdmackillop
01-09-2006, 01:30 PM
Is this what you're after?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
With Target.Range("C1:H1").Interior
Select Case Target
Case Is = 1
.ColorIndex = 6
Case Is = 2
.ColorIndex = 7
Case Is = 3
.ColorIndex = 8
Case Is = 4
.Interior.ColorIndex = 9
End Select
End With
End If
End Sub

ozibryan
01-09-2006, 05:57 PM
Thats excellent, for my education I"d ask a couple of questions.

1) This works because my A1:A3 "is not " nothing subsequently we've got a hit or selection.

2) The "with Target.Range appears to work across all the selected rows because of the intersection defined by the A* range -hence the actual row is transparent ?..

My next question is if I want to expand this to clear 'row n-1' once row n is selected can I use the same structure I experiemented a little and found that C2 for example adjut the target range down a row however is there away I can clear the previous row.

Regadless - your solution is great and I reaaly appreciate this. Thanks a lot this is a great technique


:bow:

mdmackillop
01-09-2006, 06:14 PM
The reason for using Intersect is to limit the cells which trigger the code.

To change the cells including the target cell you would use
With Target.Range("A1:H1").Interior

If you wish to change cells other than on the target row, you would use the Offset method eg
Target.offset(-1).Range("C1:H1").Interior.colorindex = xlnone
will clear the colour from the cells one row above and in the appropriate columns to the right of the Target cell.