Conditonal formatting not work when VBA apply

[waqar]

Hi All

This is my first post........Hope u people guide me......


I apply the below code for highlight Rows and coloumn and then apply Conditional Formatting but that code stop the conditional formatting. i think slight change in code enable conditional formatting to run .


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iColor As Integer

'// Amended routine found on this Web site

'// Note: Don't use IF you have Conditional

'// formating that you want to keep!


'// On error resume in case

'// user selects a range of cells

On Error Resume Next

iColor = Target.Interior.ColorIndex

'Leave On Error ON for Row offset errors


If iColor < 0 Then

iColor = 36

Else

iColor = iColor + 1

End If


'// Need this test incase Font color is the same

If iColor = Target.Font.ColorIndex Then iColor = iColor + 1


Cells.FormatConditions.Delete


'// Horizontal color banding

With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)

.FormatConditions.Add Type:=2, Formula1:="TRUE"

.FormatConditions(1).Interior.ColorIndex = 6

.FormatConditions(1).Font.Bold = True


End With


'// Vertical color banding

With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row)

.FormatConditions.Add Type:=2, Formula1:="TRUE"

.FormatConditions(1).Interior.ColorIndex = 6

.FormatConditions(1).Font.Bold = True


End With


End Sub


Regards

 

[Debraj]

Hi Waqar,

As-Salamu Alaykum


Problem is basically with the below line..

Cells.FormatConditions.Delete

* if you use this line in your code.. it will delete all the Conditional Formatting applied in the sheet..

* if you don't use this line, VBA will not change the previous formatting and all everytime you got a new Conditional formatting..


So its upto you, whom you want to give preferenece.. VBA or Conditional Formatting..


Beside that you can search a huge list of CODE abd Conditional Formatting for the same purpose what you are trying to do..


PS: One sample at the below.. as it was written by me..

http://chandoo.org/forums/topic/vba-code-to-hightlight-the-current-position-modification


Regards,

Deb..

 

[waqar]

Thanks alot Roy

I go through ur link where i find so many solution but none of them clearly understand by me as i new in VBA . Please describe me chandoo's solution and your too step by step.


Thanks in advance


Regards

 

[SirJB7]

@Debraj Roy

Hi!

السلام عليكم

Regards!

PS:

PS 2: for the community, As-Salamu Alaykum, Peace be upon you.

 

[waqar]

Hi Debraj

I am still waiting for ur reply, plz elaborate ur answer.


Regards