Help with setting range limits

  • Thread starter Thread starter Pete Csiszar
  • Start date Start date
P

Pete Csiszar

Hi All,

I have put together the following sub which changes the color of whatever
cell is active to green.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static DataField As Range
If Not DataField Is Nothing Then
DataField.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 4
Set DataField = Target

End Sub


My problem is that I have been unable to limit this routine to a specific
range of cells (actually two specific ranges) in this case D7:D14 and F7:F14

As it is right now, no matter what cell I click on, it turns green.

Any help would be much appreciated.

Pete
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
if intersect(target,Range("D7:D14,F7:F14")) is Nothing then exit sub
Target.Interior.ColorIndex = 4
End Sub

Not sure what role DateField is playing.
 
Assuming that you want to set only the Range("D7:D14") and
the Range("F7:F14") to green and that you want to only set
those ranges to green by way of VBA(not by clicking). Try
this:

Private Sub Change_To_Green()

Range("D7:D14").Interior.ColorIndex = 4
Range("F7:F14").Interior.ColorIndex = 4

End Sub

Do you have further needs for this sub?

-IA
 
Have you considered using conditional formatting?
Warning this may not work very well on a work sheet that has to many
formulas.

Select Range to be affected by formatting (in this case) "D7:D14"
Select - Menu/Formatting/Conditional Formatting
Select - Formula is (from the drop down menu in top right of dialogue box)
Type - =CELL("Row") = $D7
Press Format button and then the "Patterns" tab - to choose a cell color to
apply when formula above is true

Right click on sheet name tab and select "View Code" and paste in the
following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

In order for the active worksheet to recalculate there must be at least one
formula in each row of the formatted range (in this case the formulas could
even be in range IV7:IV14 and the formatting should still work)

I'm using Excel 2002
 
Tom,

Thanks!
DataField is just what I was what I wanted to call the highlighted cell.

What you've given me is the way to highlight all of the cells within the
range but my problem is that when I move to the next cell, the previous
cell remains green.
The point of my little sub was to just to have the currently active cell
be green and return to none upon moving to the next cell within my
specific ranges.

Does this make any sense?
TIA
Pete Csiszar P.Eng.
 
Thanks Izar,

But not quite.
I would like the active cell to be green when I click on it. When I leave
the cell and click or down arrow etc. the next active cell becomes green and
the cell just left behind returns to xlColorIndexNone.

Sorry for being vague.

Regards,
Pete
 
Back
Top