Thread: highlight individual cell as you move alone the row

Hi there,

Is there a way to highlight an individual cell as you move along a row?

For example, if you are in R1C1, then only R1C1 is highlighted and not the entire R1. I know I should do this in worksheet_selectionchange event, but not sure how should the syntax go.

Is anybody able to assist?

Thanks

oh yes, I have got that taken care of by this line of code:

If Target.Column = 5 Then
Cells(Target.Row + 1, 1).Select
End If

I remember I had to use the intercept method where it enables excel to find the intercepting range, and if the range is the intercepted by the current cell of selection, then highlight that range, which in this case is just a individual cell.

Any ideas?

Select all of the cells in the target area and add CF with a formula of

=AND(ROW()=CELL("row"),COLUMN()=CELL("col"))

and the colour of your choice.

Then add event code of

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

Sorry it's a bit hard for me to visualize how I should put

=AND(ROW()=CELL("row"),COLUMN()=CELL("col"))

in there.

What if the cells in the target range constantly change? I meant to put this feature in the front-end spreadsheet where users use it for collecting data on a daily basis, so the number of rows start to increase on a daily basis and I can hardly find myself finding a static range.

Or did I interpret your formula incorrectly?

You could add the CF dynamically.

In the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const FORMULA_INTERSECT As String = "=AND(ROW()=CELL(""row""),COLUMN()=CELL(""col""))"
Dim i As Long

    With Target
    
        For i = 1 To .FormatConditions.Count
        
            If .FormatConditions(i).Formula1 = FORMULA_INTERSECT Then
            
                .Delete
                Exit For
            End If
        Next i
        
        Application.OnTime Now + TimeSerial(0, 0, 0), "AddCondition"
    End With
End Sub

and in a standard code module

Public Sub AddCondition()
Const FORMULA_INTERSECT As String = "=AND(ROW()=CELL(""row""),COLUMN()=CELL(""col""))"

    With ActiveCell
        
        .FormatConditions.Add Type:=xlExpression, _
                              Formula1:=FORMULA_INTERSECT
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
        
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.399945066682943
        End With
        
        .FormatConditions(1).StopIfTrue = False
    End With
    
    Application.ScreenUpdating = True
End Sub

wow, I'm grateful for your response.

However, I need some clarification on how the code works because I had thought it was actually less sophisticated than this and I am even struggling trying to understand what the first line does...

So, if you don't mind, could you explain what each of the line does?

It isn't that sophisticated.

In essence, it does this:
- when a cell is selected, delete the CF condition if it is already set
- then set the CF condition for the active cell

The standard code module is required because setting a DV in the cell after deleting causes some sort of timing problem and doesn't work, so we have to break the dependency by delegating that code to another procedure.

Thinking about it some more, we can remove that delegating

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const FORMULA_INTERSECT As String = "=AND(ROW()=CELL(""row""),COLUMN()=CELL(""col""))"
Dim formulaFound As Boolean
Dim i As Long

    With Target
    
        formulaFound = False
        
        For i = 1 To .FormatConditions.Count
        
            If .FormatConditions(i).Formula1 = FORMULA_INTERSECT Then
            
                formulaFound = True
                Exit For
            End If
        Next i
        
        If Not formulaFound Then
        
            .FormatConditions.Add Type:=xlExpression, _
                                  Formula1:=FORMULA_INTERSECT
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
            
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0.399945066682943
            End With
            
            .FormatConditions(1).StopIfTrue = False
        End If
    End With
    
    Application.ScreenUpdating = True
End Sub

Sorry I may have to break it down part by part:

what does this do?

"=AND(ROW()=CELL(""row""),COLUMN()=CELL(""col""))"

I am guessing it is used to represent the interception cell between a col and row?

In essence it is just identifying the activecell, a formula for CF to work with, so that only the activecell shows the format.

pretty deep. hmmm.

Thank you.

I was working on another way to do it, and the code below seems to do the same thing.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not IsNumeric(Target) Then
        Target.ClearContents
        MsgBox "this is not a number!"
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Cells.Interior.ColorIndex = xlNone
    Cells.Font.Size = 14
    Cells(2, Target.Column).Interior.ColorIndex = 27
    Cells(Target.Row, 1).Interior.ColorIndex = 26
    
    With Target
        .Interior.ColorIndex = 43
        .Font.Size = 18
    End With
    
    If Not IsNumeric(Target) Then
        Target.ClearContents
        MsgBox "this is not number"
    End If
    If Target.Row > 11 Then
    Cells(4, Target.Column + 1).Select
    Cells(2, Target.Column + 1).Interior.ColorIndex = 27
    End If
    
    

End Sub

Yeah but look at this line

    Cells.Interior.ColorIndex = xlNone

This will wipe out any cell fill colours on that sheet - that is bad, very bad! My code leaves it alone.