VBA - Conditional Formatting multiple cases

A

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,434
Hi All,

I have some files with a lot of sheets and each sheet has different ranges that I am using Conditional Formatting in. It works fine. But every few weeks, I am having to sit down and clean all the extra CFs from the file because after running the macros on those files, the cells with CFs get copied onto other ranges within the same broader range.
Because of this duplication of CFs, the files gets slower and slower in few weeks time.
So, I thought of getting rid of all CFs and instead tried to use VBA to format cells. I have got the following so far.
Code: 
         With Range("D15:Q112")                        
                         For Each Ar In .Areas
                                If Range("B" & Ar.Row) = "Vacant" Then
                                Range("C" & Ar.Row).Interior.Color = RGB(0, 0, 0)
                                    Else
                                    Select Case Ar
                                        Case "OFF"
                                            Ar.Interior.Color = xlNone
                                            Ar.Font.Color = RGB(255, 0, 0)
                                            Ar.Font.Bold = True
                                        
                                        Case "Leave"
                                            Ar.Interior.Color = RGB(51, 102, 153)
                                            Ar.Font.Color = RGB(255, 255, 255)
                                            Ar.Font.Bold = False
                                        
                                        Case "Relief"
                                            Ar.Interior.Color = RGB(255, 0, 0)
                                            Ar.Font.Color = RGB(255, 255, 255)
                                            Ar.Font.Bold = False
                                        
                                    End Select
                                    Select Case Ar.Offset(0, 17)
                                        Case 1
                                            Ar.Interior.Color = RGB(189, 215, 238)
                                            Ar.Font.Color = RGB(0, 0, 0)
                                            Ar.Font.Bold = False
                                            
                                        Case 2
                                            Ar.Interior.Color = RGB(246, 123, 0)
                                            Ar.Font.Color = RGB(255, 0, 0)
                                            Ar.Font.Bold = False
                                            
                                        Case 3
                                            Ar.Interior.Color = RGB(178, 178, 178)
                                            Ar.Font.Color = RGB(255, 0, 0)
                                            Ar.Font.Bold = False
                                            
                                            
                                    End Select
                                End If
                        Next
                    End With

This is not working and giving me Run Time Mismatch Error 13.

Any help in fixing this would be greatly appreciated.

Thanks
Asad
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Code: 
For Each Ar In .Areas

This loops through sub-areas within a range. Areas can be multiple cells.

Later in the code...
Code: 
Select Case Ar
        Case "OFF"
...you treat the area as if it is one cell. That throws the type mismatch error if the area is multiple cells

I'm not sure what you are trying to do. So I can't suggest the appopreat fix.

This would reference the first cell in an area

Code: 
Select Case Ar[B](1).Value[/B]
        Case "OFF"
 
Upvote 0
AlphaFrog said:
Code: 
For Each Ar In .Areas

This loops through sub-areas within a range. Areas can be multiple cells.

Later in the code...
Code: 
Select Case Ar
        Case "OFF"
...you treat the area as if it is one cell. That throws the type mismatch error if the area is multiple cells
Click to expand...

So If I use
Code: 
For Each Ar in Range("D15:Q112")

Will this fix it?
All I am trying to do is loop through each cell in that range and apply the conditional formatting based on the Case scenarios as listed.
 
Upvote 0
No.

Try this...

Code: 
 [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ar [COLOR=darkblue]In[/COLOR] Range("D15:Q112").Areas
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] ar
        [COLOR=darkblue]If[/COLOR] Range("B" & cell.Row) = "Vacant" [COLOR=darkblue]Then[/COLOR]
        Range("C" & cell.Row).Interior.Color = RGB(0, 0, 0)
            [COLOR=darkblue]Else[/COLOR]
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] cell
                [COLOR=darkblue]Case[/COLOR] "OFF"
                    cell.Interior.Color = xlNone
                    cell.Font.Color = RGB(255, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]True[/COLOR]
                
                [COLOR=darkblue]Case[/COLOR] "Leave"
                    cell.Interior.Color = RGB(51, 102, 153)
                    cell.Font.Color = RGB(255, 255, 255)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                
                [COLOR=darkblue]Case[/COLOR] "Relief"
                    cell.Interior.Color = RGB(255, 0, 0)
                    cell.Font.Color = RGB(255, 255, 255)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] cell.Offset(0, 17)
                [COLOR=darkblue]Case[/COLOR] 1
                    cell.Interior.Color = RGB(189, 215, 238)
                    cell.Font.Color = RGB(0, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                    
                [COLOR=darkblue]Case[/COLOR] 2
                    cell.Interior.Color = RGB(246, 123, 0)
                    cell.Font.Color = RGB(255, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                    
                [COLOR=darkblue]Case[/COLOR] 3
                    cell.Interior.Color = RGB(178, 178, 178)
                    cell.Font.Color = RGB(255, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                    
                    
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cell
Next ar
 
Last edited:
Upvote 0
AlphaFrog said:
No.

Try this...

Code: 
 [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ar [COLOR=darkblue]In[/COLOR] Range("D15:Q112").Areas
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] ar
        [COLOR=darkblue]If[/COLOR] Range("B" & cell.Row) = "Vacant" [COLOR=darkblue]Then[/COLOR]
        Range("C" & cell.Row).Interior.Color = RGB(0, 0, 0)
            [COLOR=darkblue]Else[/COLOR]
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] cell
                [COLOR=darkblue]Case[/COLOR] "OFF"
                    cell.Interior.Color = xlNone
                    cell.Font.Color = RGB(255, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]True[/COLOR]
                
                [COLOR=darkblue]Case[/COLOR] "Leave"
                    cell.Interior.Color = RGB(51, 102, 153)
                    cell.Font.Color = RGB(255, 255, 255)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                
                [COLOR=darkblue]Case[/COLOR] "Relief"
                    cell.Interior.Color = RGB(255, 0, 0)
                    cell.Font.Color = RGB(255, 255, 255)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] cell.Offset(0, 17)
                [COLOR=darkblue]Case[/COLOR] 1
                    cell.Interior.Color = RGB(189, 215, 238)
                    cell.Font.Color = RGB(0, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                    
                [COLOR=darkblue]Case[/COLOR] 2
                    cell.Interior.Color = RGB(246, 123, 0)
                    cell.Font.Color = RGB(255, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                    
                [COLOR=darkblue]Case[/COLOR] 3
                    cell.Interior.Color = RGB(178, 178, 178)
                    cell.Font.Color = RGB(255, 0, 0)
                    cell.Font.Bold = [COLOR=darkblue]False[/COLOR]
                    
                    
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cell
Next ar
Click to expand...


Thanks a lot Alphafrog
This is perfect.

Asad
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Conditional Formatting Macro
Replies
2
Views
167
rlv01
R
L
  • Question
Conditional Formatting vba + Reverting the Conditional Formatting if no data
Replies
1
Views
139
leila
L
L
  • Solved
Conditional Formatting using VBA
Replies
1
Views
121
Joe4
Joe4
L
  • Question
VBA to export recurring meeting data
Replies
1
Views
546
John_w
J
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
180
rollis13
rollis13

Forum statistics

Threads
1,214,523
Messages
6,120,039
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top