+ Reply to Thread
Results 1 to 5 of 5

Formatting with colors with if condition in VBA

Hybrid View

kishoremcp Formatting with colors with... 01-23-2013, 01:24 PM
FDibbins Re: Formatting with colors... 01-23-2013, 01:37 PM
kishoremcp Re: Formatting with colors... 01-23-2013, 01:40 PM
kishoremcp Re: Formatting with colors... 01-23-2013, 01:55 PM
smugglersblues Re: Formatting with colors... 01-23-2013, 02:10 PM
  1. #1
    Forum Contributor
    Join Date
    01-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    402

    Formatting with colors with if condition in VBA

    I have 50 rows in which I need to format the entire filled cells with a pirticular format.
    The below macro is which i want to use for formatting that filled cells if the rows has a word Total then formatting should apply.

    The formatting macro which i have is

    Sub Format()
        Range("B3:J3").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("B3:J3").Select
        Selection.Font.Bold = True
    End Sub

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Formatting with colors with if condition in VBA

    why do you need a macro for this, when you do it quite easily with regular conditional formatting?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    01-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    402

    Re: Formatting with colors with if condition in VBA

    As I am trying to automate it .
    I got something here from google and it is formatting for entire row with the given condition. I need to format only the filled cells. Could you please edit the below macro and see that it will format only the cells which are filled.

    Sub test3()
    Dim i As Long, j As Long, rw As Long, bDel As Long
    Dim rng As Range, cel As Range
    Dim arrWords
    Dim xlCalc As XlCalculation
         arrWords = Array("Total") ' edit the array as required
         xlCalc = Application.Calculation
         Set rng = Range("B1:B200")
         For rw = rng.Rows(rng.Rows.Count).Row To rng.Rows(1).Row Step -1
                 For j = 0 To UBound(arrWords)
                         If InStr(1, rng(rw, 1), arrWords(j), vbTextCompare) Then
                                 bDel = True
                                 rng.Parent.Rows(rw).EntireRow.Select
                                 With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
            Selection.Font.Bold = True
                                 
                                 Exit For
                         End If
                 Next
         Next
         Application.Calculation = xlCalc
    
    End Sub

  4. #4
    Forum Contributor
    Join Date
    01-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    402

    Re: Formatting with colors with if condition in VBA

    Any help is appreciated

  5. #5
    Forum Contributor
    Join Date
    12-14-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Formatting with colors with if condition in VBA

    Here is a macro I'm using. You just need the RGB color code (google for codes).

    Sub ColorCells()
    'Change A1:M250 to your needed range
    For Each cl In Range("A1:M250")
           Select Case cl.Value
           'Yellow
           'Add or remove as many elements as you want. 
           Case "dog", "cat", "pig"
             'use google to search for RGB color codes
             cl.Interior.Color = RGB(255, 255, 0)
           'Blue
           Case "horse", "bird", "duck"
             cl.Interior.Color = RGB(153, 206, 255)
           End Select
       Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1