HIGHLIGHT CELL FROM "A TO N"

[Jagat Pavasia]

I HAVE VBA CODE BELOW, IT IS WORKING AS IT SHOULD.
BUT NOW I WANT THE CELL HIGHLIGHT WHEN I SELECT IT.

FOR EXAMPLE : IF I SELECT "B8" THEN ALL LINE FROM "A8 TO N8" SHOULD BE HIGH-LIGHTED.
IF I SELECT "C9" THEN ALL LINE FROM "A9 TO N9" SHOULD BE HIGHLIGHTED..

I HAVE TRIED CONDITIONAL FORMULA "
=OR(CELL("row")=ROW())

BUT IT IS NOT WORK FOR ME. PLEASE HELP ME,

TELL ME IF LITTLE CHANGE IN VBA CODE (if)



VBA CODE :
Private Sub worksheet_change(ByVal target As Range)
Macro1 target
Macro2 target
End Sub

Sub Macro1(target As Range)
Dim Rng As Range

If target.Count > 1 Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
Set Rng = Range("b:b,j:j,M:M")
If Not Intersect(target, Rng) Is Nothing Then
If target.Value = "**" Then target.Value = Format(Now, "m/d/yy, h:mm AM/PM")
End If

If Not Intersect(target, Range("B4:R4")) Is Nothing Then
If target.Value = "" Then
ActiveSheet.Range("B6:R6").AutoFilter Field:=target.Column
Else
ActiveSheet.Range("B6:R6").AutoFilter Field:=target.Column, Operator:=xlFilterValues, Criteria1:=CStr(target.Value)
End If
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFiltering:=True
Application.EnableEvents = True
End Sub

Sub Macro2(target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer


Set WorkRng = Intersect(Application.ActiveSheet.Range("F6:F9999,H6:H9999,K6:K9999"), target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
On Error GoTo exit_proc
Me.Unprotect
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "d/m/yy, h:mm AM/PM"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
exit_proc:
Me.Protect
End Sub

 

[Peter_SSs]

When posting, please do not use all upper case - see #14 of the Forum Rules

I HAVE TRIED CONDITIONAL FORMULA "
=OR(CELL("row")=ROW())

Perhaps you did not implement it quite correctly. Worth another try. Try this first on a brand new worksheet.

1. Select the whole of columns A:N by click & drag across the column headings.
2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =AND(CELL("row")=ROW(),CELL("col")<15) -> Format... -> On the Fill tab select the colour you want** -> Ok -> Ok
3. Right click the sheet name tab and choose 'View Code'
4. Copy and Paste the code below into the main right hand pane that opens at step 3.
5. Close the Visual Basic window & test.

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

A further advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub