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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

J
  • Question Question
Userform inconsistent responses
Replies
1
Views
163
Candyman8019
C
R
  • Question Question
Search a range for a specific color of rows, relocate those cells to another sheet, return previous sheet formatting to blank and sort secondary sheet
Replies
4
Views
345
Royalbloodi
R
C
  • Solved
Conditional Formatting Macro
Replies
2
Views
421
rlv01
R
L
  • Question Question
Conditional Formatting vba + Reverting the Conditional Formatting if no data
Replies
1
Views
294
leila
L
Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
664
Micron
M

Forum statistics

Threads
1,223,164
Messages
6,170,435
Members
452,326
Latest member
johnshaji

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