Results 1 to 2 of 2

How to use a Macro Apply Conditional Formatting to Column based on contents & color table

Threaded View

TimBZKK How to use a Macro Apply... 05-28-2014, 06:28 PM
TimBZKK Re: How to use a Macro Apply... 05-28-2014, 06:31 PM
  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    19

    How to use a Macro Apply Conditional Formatting to Column based on contents & color table

    I've been looking for an answer (or something close to one) to a question for quite some time now, and I have exhausted all of my resources.

    I have an Inventory Report Spreadsheet that currently imports an inventory report from our system, inputs it in this form, and calculates what needs to be ordered for us to keep stock through the given timeframe (i.e. based on a two week report, how much stock do we have to order to last through the NEXT two weeks). I have a table on my Config worksheet that contains a list of partial SKUs and each SKU is colored a different background color (this is a Named Range called SKUList). I also have a macro that processes the current inventory report, adds a few formulas to figure out what to order, and saves it into a new worksheet in this workbook. what I would like to accomplish is this:

    At the end of my Macro, I would like to conditionally format all of the first column (the SKU column, variable: rngSKU As Range) based on text found in the SKU list on my config page, and color the cells based on the color of each cell on the SKU list. So something like

    If text(SKUlist) Exists, then
    Set Cell Color = Corresponding SKUList Cell

    I don't necessarily need a whole Sub typed out, but I am looking for someone who might be able to explain how to do this. I am not a VBA newbie, but I am far from being proficient.

    **EDIT**

    This is my "formatting" code block:

        Columns("G:G").Select
        Selection.Cut
        Columns("C:C").Select
        Selection.Insert Shift:=xlToRight
        With Selection.Font
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = -0.499984740745262
        End With
        ActiveSheet.Range("C2", ActiveSheet.Range("C2").End(xlDown)).Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.Font.Bold = True
        Rows("1:1").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        With Selection.Font
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = -0.249977111117893
        End With
        Selection.Font.Bold = True
            Columns("A:A").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C1>0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .TintAndShade = 0
        End With
        Columns("A:A").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Zag"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Ipg"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Iot"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Ntz"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 15773696
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Steelie"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Macrn"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Ntch"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Popscl"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = 0.599963377788629
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""Sprnkl"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = 0.599963377788629
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""CgrCdy"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249946592608417
        End With
        Selection.FormatConditions(1).StopIfTrue = False
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""IOTMnt"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 14918087
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""IOTFlx"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 14918087
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""IOTA"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16772147
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""IOTSmg"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16772147
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=FIND(""ITNyln"",$A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 7922913
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    Last edited by TimBZKK; 05-28-2014 at 06:32 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating table with conditional formatting based on growing contents
    By Ash_ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2013, 11:45 AM
  2. VBA for Conditional Formatting - apply a colour based on conditions in each column.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-27-2013, 01:24 PM
  3. Replies: 5
    Last Post: 05-03-2012, 06:06 PM
  4. Macro to change font color based on contents of another column
    By Pat Parker in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2010, 12:33 AM
  5. Replies: 6
    Last Post: 12-22-2008, 06:16 PM

Tags for this Thread

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