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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

H
  • Question Question
Referencing a series name that contains data linked to a cell VBA
Replies
1
Views
58
Rijnsent
Rijnsent
H
  • Locked
  • Question Question
Help referencing a series name that contains data linked to a cell VBA
Replies
1
Views
36
Fluff
Fluff
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
308
StephenCrump
S
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
218
Candyman8019
C
J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
206
JAZ91
J

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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