+ Reply to Thread
Results 1 to 4 of 4

coloring cells based on criteria

  1. #1
    Registered User
    Join Date
    11-15-2020
    Location
    dubai
    MS-Off Ver
    2019
    Posts
    8

    coloring cells based on criteria

    I want to color the cells of column F in my excel.
    The criteria for coloring are values which are numerical.
    The scheme for color is based on following schema
    location taxi type avg lower limit avg upper limit color quantity
    location 1 gokia 5 7 Red 1
    7 8 Yellow 0
    8 9 White 2
    9 Green 5
    location total 8
    location casanova 5 8 Red 0
    8 9 Yellow 1
    9 10 White 1
    10 Green 52
    location total 54
    location orbit 20 30 Red 1
    30 33 Yellow 1
    33 36 White 14
    location 36 Green 7
    Total 23
    If a value lies on boundary i.e. 7 then it will be in lower range meaning
    gokia has 4 categories 5-7
    7-8
    8-9
    9>

    if a value is in column F is on boundary for type gokia example a value is 7 then it will belong to category 5-7
    or if a value is 9 then it will belong to category 8-9. For boundary cases of values the category will be lower range.
    Like this for Taxi type in column C values in F are different.
    so all these cells in column F have to be colored Red,Green,Yellow,White
    based on 4 categories.
    The type of of taxi are gokia,casanova,orbit.
    I want to automate this process of coloring as there is lot of data so each
    time manually selecting and adding a filter is a time consuming work.
    Attached Files Attached Files
    Last edited by poaxxafllad; 11-24-2020 at 08:17 PM.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: coloring cells based on criteria

    based on scanning the words of your post briefly, conditional formatting rules exist in excel for this very purpose.

    https://support.microsoft.com/en-us/...b-f1951ff89d7f

    https://www.excel-easy.com/data-****...ormatting.html

  3. #3
    Registered User
    Join Date
    11-15-2020
    Location
    dubai
    MS-Off Ver
    2019
    Posts
    8

    Re: coloring cells based on criteria

    There are 3000 excels in which I have to do this conditional formatting.
    So opening an excel each time and manually doing conditional formatting is a repetitive task it takes a lot of time.
    I want to automate that.

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: coloring cells based on criteria

    this code puts green shading into cells with value > 5. you can use this to copy from. this code was given by the macro recorder. took 20 seconds.
    PHP Code: 
    Sub Macro2()
    '
    Macro2 Macro
    '

    '
        
    Selection.FormatConditions.Add Type:=xlCellValueOperator:=xlGreater_
            Formula1
    :="=5"
        
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection
    .FormatConditions(1).Interior
            
    .PatternColorIndex xlAutomatic
            
    .Color 5296274
            
    .TintAndShade 0
        End With
        Selection
    .FormatConditions(1).StopIfTrue False
    End Sub 
    I selected the range manually before recording. to add the selection line first yourself, simply put this at the top of the code:
    PHP Code: 
    range("a1""b7').select 
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro Code Needed for Coloring Various rows based on the Criteria of the Row
    By Adamsc21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2017, 06:56 PM
  2. Replies: 22
    Last Post: 03-16-2017, 09:42 PM
  3. [SOLVED] Coloring cells based on value in another cell
    By killerthun in forum Excel General
    Replies: 2
    Last Post: 08-16-2012, 08:21 AM
  4. Coloring 3 cells based on condition in 1
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-20-2009, 11:55 AM
  5. Coloring rows based on criteria
    By Marcus Gee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2008, 10:51 PM
  6. Coloring Based on Text Criteria in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-19-2007, 12:31 PM
  7. SchoolWork - Coloring certain cells by text criteria
    By Rick_B in forum Excel General
    Replies: 3
    Last Post: 08-01-2005, 09:14 AM

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