Sorry my bad.
It's not strange, as in my side it also still leave the last highlighted row,
while the image in my post, I make another code to get rid off that last highlighted row
.
Here is my way to get rid off that last highlighted row :
Please make a new module, so there is no "Option Explicit",
copy the code below then paste to the new module.
Sub setFill()
Set Rng = Sheets("Sheet1").Range("A:S") 'change according to the need
With Rng
For Each FC In .FormatConditions
If FC.Type = xlExpression And (FC.Formula1 = "=OR(CELL(""row"")=CELL(""row"",A1))") Then Exit Sub
Next
Application.EnableEvents = False
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(CELL(""row"")=CELL(""row"",A1))"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.Color = 13551615
Application.EnableEvents = True
End With
End Sub
Sub stopFill()
Set Rng = Sheets("Sheet1").Range("A:S") 'change according to the need
With Rng
Application.EnableEvents = False
For Each FC In .FormatConditions
If FC.Type = xlExpression And (FC.Formula1 = "=OR(CELL(""row"")=CELL(""row"",A1))") Then FC.Delete
Next
Application.EnableEvents = True
End With
End Sub
And for the worksheet module (again, please no "Option Explicit") :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lr = Range("A" & Rows.Count).End(xlUp).Row
cl = Cells(1, Columns.Count).End(xlToLeft).Column
If ActiveCell.Column > cl Or ActiveCell.Row > lr Then Call stopFill: Exit Sub
Call setFill
Target.Calculate
End Sub
The code above set the last column header number (cl) and the the last row number in column A (lr)
If the active cell is bigger than cl or bigger than lr,
it will remove the Conditional Formatting in the sheet (by calling the stopFill Sub),
this stopFill Sub will not remove the "fill" formatting in the sheet.
Then it directly exit the sub.
If the active cell is smaller than cl or smaller than lr (so it's within the "table range"),
then it put the Conditional Formatting in the sheet (by calling the setFill Sub).
setFill Sub won't do Conditional Formatting each time it's called,
as it will check first whether the sheet has any Conditional Formatting or not (yes/no).
If yes, then it directly exit the sub. If no, then it write the Conditional Formatting.
PS:
Please don't forget to change the code where it read
Range("A:S") according to your need,
and also change the
("Sheet1") according to your need.
Bookmarks