have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!

hi
try this and see if it's what you want..
this is worksheet code. right click the sheet and click view code. paste the
below macro into the code window. only works on the sheet that you paste the
code into.
Selecting outside the range A1:CP300 will not highlight cells(rows). you can
adjust the range to suit.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim ac As Range
Set r = Range("A1:CP300")
Set ac = ActiveCell
If Not Intersect(Target, r) Is Nothing Then
Target.EntireRow.Select
ac.Activate
End If
End Sub

regards
FSt1

"Mike" wrote:

have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!

How about this lets say i want to click cell k6 & i want to high light that
entire row from b6 to af6

"FSt1" wrote:

hi
try this and see if it's what you want..
this is worksheet code. right click the sheet and click view code. paste the
below macro into the code window. only works on the sheet that you paste the
code into.
Selecting outside the range A1:CP300 will not highlight cells(rows). you can
adjust the range to suit.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim ac As Range
Set r = Range("A1:CP300")
Set ac = ActiveCell
If Not Intersect(Target, r) Is Nothing Then
Target.EntireRow.Select
ac.Activate
End If
End Sub

regards
FSt1

"Mike" wrote:

have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!

hi
in your original post, you specified range A2 to CP300 so i see that at the
range with this line

Set r = Range("A1:CP300")

you would have to change that to RAnge("B2:AF300")

As i have written it, anywhere you click on a row with high light the row
for the defined range and the cell clicked on will be the active cell.

try it and see. post back if problems.
Regards
FSt1



"Mike" wrote:

How about this lets say i want to click cell k6 & i want to high light that
entire row from b6 to af6

"FSt1" wrote:

hi
try this and see if it's what you want..
this is worksheet code. right click the sheet and click view code. paste the
below macro into the code window. only works on the sheet that you paste the
code into.
Selecting outside the range A1:CP300 will not highlight cells(rows). you can
adjust the range to suit.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim ac As Range
Set r = Range("A1:CP300")
Set ac = ActiveCell
If Not Intersect(Target, r) Is Nothing Then
Target.EntireRow.Select
ac.Activate
End If
End Sub

regards
FSt1

"Mike" wrote:

have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!

Hi Mike,

Perhaps try this sample of row highlighting to see if it is what you want to
happen. We can expand it to cover your range but take a look at what
happens if you click on any cell in Range("B8:K22").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

Copy and paste in a test sheet module and then click any cell in the range
B8:K22. Then click outside of that range to see what happens, which should
be nothing.

Adjusting the values of i and j plus a few changes to the code to adopt your
range will be necessary.

HTH
Regards,
Howard

"Mike" wrote in message
...
have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr
cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!

This is what i have & it works it is high lighting B column now i want to
high light AH column at the same time & from there i need to do rows 43 to 74
,80 to 111,117 to 148,154 to 185,191 to 222, 228 to 259 also in the same
manner also B & AH columns have a conditional colour format red green or
yellow when these colours are in place it will not high light i believe if
you or someone can make this happen YOU MUST BE A GOD

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim K As Integer
i = 2
j = 6
K = ActiveCell.Column()
Set Data = Range("B6:AH37")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 6 Or ActiveCell.Row 37 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 54 Then
Exit Sub
End If

ActiveCell.Offset(0, -(K - i)). _
Resize(1).Interior.ColorIndex = 40

End Sub

"L. Howard Kittle" wrote:

Hi Mike,

Perhaps try this sample of row highlighting to see if it is what you want to
happen. We can expand it to cover your range but take a look at what
happens if you click on any cell in Range("B8:K22").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

Copy and paste in a test sheet module and then click any cell in the range
B8:K22. Then click outside of that range to see what happens, which should
be nothing.

Adjusting the values of i and j plus a few changes to the code to adopt your
range will be necessary.

HTH
Regards,
Howard

"Mike" wrote in message
...
have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr
cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!


.

Hi Mike,

Try this which highlights the column B and AH cell of activecell row within
("B6:AH37").

The activecell column rows 43 to 74 and 80 to 111 are highlighted also.
(You can probably add the other four ranges you want highlighted by adding
additional lines of code and changing the offset value for each.)

Don't know what to tell you about the conditional format coloring. In my
tests the CF coloring will prevail over the VBA code coloring.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim RowData As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim l As Integer
i = 2
j = 34
m = 37

k = ActiveCell.Column()
l = ActiveCell.Row()

Set Data = Range("B6:AH37")
Set RowData = Range("B38:ah300")
Data.Interior.ColorIndex = xlNone
RowData.Interior.ColorIndex = xlNone

If ActiveCell.Row < 6 Or ActiveCell.Row 37 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 34 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)).Interior.ColorIndex = 40
ActiveCell.Offset(0, (j - k)).Interior.ColorIndex = 40

ActiveCell.Offset((m - l) + 6, 0). _
Resize(32, 1).Interior.ColorIndex = 40
ActiveCell.Offset((m - l) + 43, 0). _
Resize(32, 1).Interior.ColorIndex = 40

End Sub

HTH
Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hi Mike,

Perhaps try this sample of row highlighting to see if it is what you want
to happen. We can expand it to cover your range but take a look at what
happens if you click on any cell in Range("B8:K22").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

Copy and paste in a test sheet module and then click any cell in the range
B8:K22. Then click outside of that range to see what happens, which
should be nothing.

Adjusting the values of i and j plus a few changes to the code to adopt
your range will be necessary.

HTH
Regards,
Howard

"Mike" wrote in message
...
have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr
cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!

THANKS THIS WORKED WELL my next guestion is i want to protect each sheet when
i try & do this the macro does not work can i please get some help with this.
This is the Macro i ended up using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim aq As Integer
i = 2
j = 34

k = ActiveCell.Column()
aq = ActiveCell.Column()

Set Data = Range("B6:AH259, AH6:BA259")
Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 6 Or ActiveCell.Row 259 Or _
ActiveCell.Column < 11 Or ActiveCell.Column 54 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)).Interior.ColorIndex = 37
ActiveCell.Offset(0, (j - k)).Interior.ColorIndex = 37

End Sub













"L. Howard Kittle" wrote:

Hi Mike,

Try this which highlights the column B and AH cell of activecell row within
("B6:AH37").

The activecell column rows 43 to 74 and 80 to 111 are highlighted also.
(You can probably add the other four ranges you want highlighted by adding
additional lines of code and changing the offset value for each.)

Don't know what to tell you about the conditional format coloring. In my
tests the CF coloring will prevail over the VBA code coloring.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim RowData As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim l As Integer
i = 2
j = 34
m = 37

k = ActiveCell.Column()
l = ActiveCell.Row()

Set Data = Range("B6:AH37")
Set RowData = Range("B38:ah300")
Data.Interior.ColorIndex = xlNone
RowData.Interior.ColorIndex = xlNone

If ActiveCell.Row < 6 Or ActiveCell.Row 37 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 34 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)).Interior.ColorIndex = 40
ActiveCell.Offset(0, (j - k)).Interior.ColorIndex = 40

ActiveCell.Offset((m - l) + 6, 0). _
Resize(32, 1).Interior.ColorIndex = 40
ActiveCell.Offset((m - l) + 43, 0). _
Resize(32, 1).Interior.ColorIndex = 40

End Sub

HTH
Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hi Mike,

Perhaps try this sample of row highlighting to see if it is what you want
to happen. We can expand it to cover your range but take a look at what
happens if you click on any cell in Range("B8:K22").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

Copy and paste in a test sheet module and then click any cell in the range
B8:K22. Then click outside of that range to see what happens, which
should be nothing.

Adjusting the values of i and j plus a few changes to the code to adopt
your range will be necessary.

HTH
Regards,
Howard

"Mike" wrote in message
...
have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr
cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!




.

Unprotect then re-protect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim aq As Integer
i = 2
j = 34

k = ActiveCell.Column()
aq = ActiveCell.Column()
Set Data = Range("B6:AH259, AH6:BA259")

If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme" 'edit pword to suit

Data.Interior.ColorIndex = xlNone
If ActiveCell.Row < 6 Or ActiveCell.Row 259 Or _
ActiveCell.Column < 11 Or ActiveCell.Column 54 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)).Interior.ColorIndex = 37
ActiveCell.Offset(0, (j - k)).Interior.ColorIndex = 37
End If

ActiveSheet.Protect Password:="justme"

End Sub


Gord Dibben MS Excel MVP

On Sat, 12 Dec 2009 21:21:01 -0800, Mike
wrote:

THANKS THIS WORKED WELL my next guestion is i want to protect each sheet when
i try & do this the macro does not work can i please get some help with this.
This is the Macro i ended up using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim aq As Integer
i = 2
j = 34

k = ActiveCell.Column()
aq = ActiveCell.Column()

Set Data = Range("B6:AH259, AH6:BA259")
Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 6 Or ActiveCell.Row 259 Or _
ActiveCell.Column < 11 Or ActiveCell.Column 54 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)).Interior.ColorIndex = 37
ActiveCell.Offset(0, (j - k)).Interior.ColorIndex = 37

End Sub













"L. Howard Kittle" wrote:

Hi Mike,

Try this which highlights the column B and AH cell of activecell row within
("B6:AH37").

The activecell column rows 43 to 74 and 80 to 111 are highlighted also.
(You can probably add the other four ranges you want highlighted by adding
additional lines of code and changing the offset value for each.)

Don't know what to tell you about the conditional format coloring. In my
tests the CF coloring will prevail over the VBA code coloring.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim RowData As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim l As Integer
i = 2
j = 34
m = 37

k = ActiveCell.Column()
l = ActiveCell.Row()

Set Data = Range("B6:AH37")
Set RowData = Range("B38:ah300")
Data.Interior.ColorIndex = xlNone
RowData.Interior.ColorIndex = xlNone

If ActiveCell.Row < 6 Or ActiveCell.Row 37 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 34 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)).Interior.ColorIndex = 40
ActiveCell.Offset(0, (j - k)).Interior.ColorIndex = 40

ActiveCell.Offset((m - l) + 6, 0). _
Resize(32, 1).Interior.ColorIndex = 40
ActiveCell.Offset((m - l) + 43, 0). _
Resize(32, 1).Interior.ColorIndex = 40

End Sub

HTH
Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hi Mike,

Perhaps try this sample of row highlighting to see if it is what you want
to happen. We can expand it to cover your range but take a look at what
happens if you click on any cell in Range("B8:K22").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

Copy and paste in a test sheet module and then click any cell in the range
B8:K22. Then click outside of that range to see what happens, which
should be nothing.

Adjusting the values of i and j plus a few changes to the code to adopt
your range will be necessary.

HTH
Regards,
Howard

"Mike" wrote in message
...
have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr
cell
on any row is it possible to high light that whole row if there is a way
please tell me how!!!




.