Thread: Conditional Formatting VBA

Hi,
I am using following macro for conditional formatting (6 conditions) and this working well. But the only problem I have is that when I delete the data from column it does not retrieve to no fill (no color). I have my values in column M, O & Q. Please help me.

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyPlage = Range("M2:Q300")
    
    For Each Cell In MyPlage
    
        Select Case Cell.Value
        
         Case Is = "First Reminder Due"
            Cell.EntireRow.Interior.ColorIndex = 50
        
        Case Is = "First Reminder Sent"
            Cell.EntireRow.Interior.ColorIndex = 10
                
        Case Is = "Second Reminder Due"
            Cell.EntireRow.Interior.ColorIndex = 40
            
        Case Is = "Second Reminder Sent"
            Cell.EntireRow.Interior.ColorIndex = 46
        
        Case Is = "Final Reminder Due"
            Cell.EntireRow.Interior.ColorIndex = 38
            
        Case Is = "Final Reminder Sent"
            Cell.EntireRow.Interior.ColorIndex = 3
                
        End Select
    
    Next
End Sub

I have tried to add following code but then nothing works i.e. there is no color change even for above conditions.

Case Is = ""
            Cell.EntireRow.Interior.ColorIndex = xlNone or 0

Try this,

Case Else
            cell.EntireRow.Interior.ColorIndex = 0

or

Case Empty
            cell.EntireRow.Interior.ColorIndex = 0

hope it helps,
Mohammad

No it doesn't help as it reads any of the empty cell from M to Q while I want that when there is no value in columns M,O and Q then It should change to white background / no color. But if there is any given value in any of the three columns then it should change the color.
Further Can I have more than one change sheet event code in same worksheet as I am trying to wirte down sepeate codes for each column M, O & Q but when I change the names of macros like

Private Sub Worksheet_Change1(ByVal Target As Range)
 and Private Sub Worksheet_Change2(ByVal Target As Range)

then it does not work.
I hope now its more clear and you can suggest something to solve this issue.

Thanks

Did you try the second code I provided?

Case Empty
            cell.EntireRow.Interior.ColorIndex = 0

this checks if the cell is empty ore not and if it is empty it will clear the formatting.

about having more than 1 change functions you can't do that, however you can in the same function check the changed range using the Target argument and write your action for example:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M2:M300")) Is Nothing Then
        'do some actions
    End If
    If Not Intersect(Target, Range("N2:N300")) Is Nothing Then
        'do some actions
    End If
    If Not Intersect(Target, Range("O2:O300")) Is Nothing Then
         'do some actions
    End If
End Sub

I tried your code of empty cell but its not getting the required result as I told you before that it reads any of the empty cell from M to Q which I dont want. I explain you my logic and then I think you can better create a code to fulfill the purpose. What I want is that When there is "First Reminder Due" or "First Reminder Sent" in Column M then row color should be "50" or "10" respectively. Similarly when there is "Second Reminder Due" or "Second Reminder Sent" in Column O then color should be "40" or "46" respectively and finally when there is "Final Reminder Due" or "Final Reminder Sent" in Column Q then color should be "38" or "3" respectively. But when there is no value in Column M, O or Q then there should be no row color. Or you can restict it to Column M only which means when Column M is empty then row background color should be white.
Hope now you can better help me.

Thans for your concen.

Dear AssifShabbir,

I hope I have understood you correctly this time,
please try the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyPlage = Range("M2:Q300")
If Not Intersect(Target, MyPlage) Is Nothing Then
    For Each cell In Intersect(Target, MyPlage)
    
        Select Case cell.Value
        
         Case Is = "First Reminder Due"
            cell.EntireRow.Interior.ColorIndex = 50
        
        Case Is = "First Reminder Sent"
            cell.EntireRow.Interior.ColorIndex = 10
                
        Case Is = "Second Reminder Due"
            cell.EntireRow.Interior.ColorIndex = 40
            
        Case Is = "Second Reminder Sent"
            cell.EntireRow.Interior.ColorIndex = 46
        
        Case Is = "Final Reminder Due"
            cell.EntireRow.Interior.ColorIndex = 38
            
        Case Is = "Final Reminder Sent"
            cell.EntireRow.Interior.ColorIndex = 3
        Case Empty
            cell.EntireRow.Interior.ColorIndex = 0
                
        End Select
    
    Next
End If
End Sub

I hope this helps,
Mohammad

Thanks for your reply but it is not working as I explained you before that there are five columns when we set range M:Q and the code reads information from all five columns when we set Case Empty while what I desire is that when only three M,O & Q are empty simultaneously then there should be no color or even when M is empty then row color should be white.
However I have found a solution by putting this 7th condition in normal conditional formatting which is when Column M is empty then row color should be white.
But still I want your help as the code above (macro) is set to autorun (change by value) and I have some date (Today's date) formulae in Column M, O and Q. Problem is that when date is changed some fields are populated like "First Reminder Due" etc but the code does not treat it as change in value and it does not triggered unless I put something in any of the column manually.
Is there any way for triggering the code without putting something manually? I mean it should read the information in column M, O & Q when these are changed as a result of formula when the worksheet is opened.
I hope I am not bothering you. Thanks for your help.

maybe you want to use the WorkSheet Calculate event instead of the change event like below:

Private Sub Worksheet_Calculate()
'
'Place your code here
'
End Sub

Yes It works. Thanks alot for your throughout support.

you are welcome