highlighting cell with different color and rows and column with different color

[kamranyd]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection.Worksheet.Cells.FormatConditions.Delete
Rows(ActiveCell.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(ActiveCell.Row).FormatConditions(1).Interior.Color = RGB(255, 128, 128)
Columns(ActiveCell.Column).FormatConditions.Add xlExpression, , "TRUE"
Columns(ActiveCell.Column).FormatConditions(2).Interior.Color = RGB(0, 204, 255)

End Sub

hello everyone. these codes work fine in showing row and columns highlighted when I select any cell. but how to give selected cell different color.

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection.Worksheet.Cells.FormatConditions.Delete
ActiveCell.FormatConditions.Add xlExpression, , True
ActiveCell.FormatConditions(1).Interior.Color = vbYellow
Rows(ActiveCell.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(ActiveCell.Row).FormatConditions(2).Interior.Color = RGB(255, 128, 128)
Columns(ActiveCell.Column).FormatConditions.Add xlExpression, , "TRUE"
Columns(ActiveCell.Column).FormatConditions(3).Interior.Color = RGB(0, 204, 255)
End Sub

 

[kamranyd]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection.Worksheet.Cells.FormatConditions.Delete
ActiveCell.FormatConditions.Add xlExpression, , True
ActiveCell.FormatConditions(1).Interior.Color = vbYellow
Rows(ActiveCell.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(ActiveCell.Row).FormatConditions(2).Interior.Color = RGB(255, 128, 128)
Columns(ActiveCell.Column).FormatConditions.Add xlExpression, , "TRUE"
Columns(ActiveCell.Column).FormatConditions(3).Interior.Color = RGB(0, 204, 255)
End Sub

thank you very much really.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kamranyd]

Private Sub worksheet_SelectionChange(ByVal Target As Range)

Dim RngRow As Range
Dim FndRng As Range
Dim Rng As Range
Dim RowRng As Range

'ActiveSheet.Unprotect

Selection.Worksheet.Cells.FormatConditions.Delete

Set FndRng = Intersect(Target, Range("A2:G1000"))

If Not FndRng Is Nothing Then
    Set RngRow = Range("A2:G1000")

ActiveCell.FormatConditions.Add xlExpression, , True
ActiveCell.FormatConditions(1).Interior.Color = RGB(0, 0, 255)

Rows(ActiveCell.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(ActiveCell.Row).FormatConditions(2).Interior.Color = RGB(255, 0, 0)

End If

Set Rng = Intersect(Target, Range("H2:DW1000"))

If Not Rng Is Nothing Then
    Set RowRng = Range("H2:DW1000")
    

ActiveCell.FormatConditions.Add xlExpression, , True
ActiveCell.FormatConditions(1).Interior.Color = RGB(128, 458, 128)
   
Rows(ActiveCell.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(ActiveCell.Row).FormatConditions(2).Interior.Color = RGB(255, 0, 0)

End If
End Sub

I did changes in these codes from my previous codes. but these codes give me error (run-time error'9' saying "subscript out of range" why

 

[Fluff]

Which line gives the error?

 

[kamranyd]

Rows(ActiveCell.Row).FormatConditions(2).Interior.Color = RGB(255, 0, 0)

this line

 

[Fluff]

The only reason you would get a subscript out of range error on that line, is if the condition hadn't been added, but I can think of no reason why that would happen. Your code work quite happily for me.

 

[kamranyd]

The only reason you would get a subscript out of range error on that line, is if the condition hadn't been added, but I can think of no reason why that would happen. Your code work quite happily for me.

these codes are in sheet2. when I copy data from sheet1 to sheet2 than this error get shown. otherwise codes are working fine if I don't copy data from sheet1.

 

[Fluff]

I cannot replicate behaviour.