Results 1 to 11 of 11

Automatically add a star icon based on color of the cell

Threaded View

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Hawaii
    MS-Off Ver
    Excel 2003
    Posts
    34

    Automatically add a star icon based on color of the cell

    I have a timeline generator for project tracking (auto colors cells based on phases). For the Deploy phase, I have the cells colored green (this was all done in VBA, not conditional formatting). Now I want to place a star in the cells (centered in the cell) that represent the deploy phase (as well as the color): Here is what current code:

    Sub RenderProjects()

    Dim row, col
    Dim t As String
    Dim timelineRow
    Dim lp
    Dim startDate As Date
    Dim endDate As Date
    Dim startWeek, endWeek
    Dim weekLp
    Dim targetColor As Long

    timelineRow = 4
    row = 4
    col = 1

    While Worksheets("Projects").Cells(row, col).Value <> ""
    t = Worksheets("Projects").Cells(row, 1).Value + Chr(10) + Worksheets("Projects").Cells(row, 2).Value + Chr(10) + Worksheets("Projects").Cells(row, 3).Value
    Worksheets("Timeline").Cells(timelineRow, 1).Value = t
    Worksheets("Timeline").Cells(timelineRow, 2).Value = Worksheets("Projects").Cells(row, 4).Value

    t = Worksheets("Projects").Cells(row, 5).Value
    If (t = Range("On_Track").Value) Then
    targetColor = Range("On_Track").Interior.color
    Worksheets("Timeline").Cells(timelineRow, 1).Interior.color = targetColor
    End If
    If (t = Range("At_Risk").Value) Then
    targetColor = Range("At_Risk").Interior.color
    Worksheets("Timeline").Cells(timelineRow, 1).Interior.color = targetColor
    End If
    If (t = Range("Off_Track").Value) Then
    targetColor = Range("Off_Track").Interior.color
    Worksheets("Timeline").Cells(timelineRow, 1).Interior.color = targetColor
    End If
    If (t = Range("Pending").Value) Then
    targetColor = Range("Pending").Interior.color
    Worksheets("Timeline").Cells(timelineRow, 1).Interior.color = targetColor

    End If


    col = 6
    For lp = 1 To 8
    t = Worksheets("Projects").Cells(row, 6 + (lp - 1) * 2).Value
    If (t <> "") Then
    startDate = Worksheets("Projects").Cells(row, 6 + (lp - 1) * 2).Value
    endDate = Worksheets("Projects").Cells(row, 7 + (lp - 1) * 2).Value
    startWeek = Application.WorksheetFunction.WeekNum(startDate)
    endWeek = Application.WorksheetFunction.WeekNum(endDate)
    targetColor = GetCategoryColor(lp)
    For weekLp = startWeek To endWeek
    Worksheets("Timeline").Cells(timelineRow, 4 + weekLp).Interior.color = targetColor
    Next weekLp
    End If


    Next lp






    timelineRow = timelineRow + 1
    row = row + 1
    Wend

    End Sub


    Function GetCategoryColor(ByVal lp As Integer) As Long

    Dim PlanningColor As Long
    Dim RequirementsColor As Long
    Dim ArchColor As Long
    Dim BuildColor As Long
    Dim TestColor As Long
    Dim UATColor As Long
    Dim DeployColor As Long
    Dim WarrantyColor As Long

    ' GRAB THE BACKGROUND COLORS FROM THE Projects SHEET TO COLOR CODE PROJECT STATES
    PlanningColor = Range("PlanningColor").Interior.color
    RequirementsColor = Range("RequirementsColor").Interior.color
    ArchColor = Range("ArchColor").Interior.color
    BuildColor = Range("BuildColor").Interior.color
    TestColor = Range("TestColor").Interior.color
    UATColor = Range("UATColor").Interior.color
    DeployColor = Range("DeployColor").Interior.color
    WarrantyColor = Range("WarrantyColor").Interior.color

    Dim color

    Select Case lp

    Case 1
    color = PlanningColor
    Case 2
    color = RequirementsColor
    Case 3
    color = ArchColor
    Case 4
    color = BuildColor
    Case 5
    color = TestColor
    Case 6
    color = UATColor
    Case 7
    color = DeployColor
    Case 8
    color = WarrantyColor
    End Select

    GetCategoryColor = color

    End Function
    Attached Images Attached Images
    Last edited by kre30a; 08-08-2016 at 12:34 PM. Reason: Added image

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to automatically hide a row based cell color
    By clawren4 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2014, 12:28 PM
  2. Automatically delete cells based on adjacent cell color
    By chromecarz00 in forum Excel General
    Replies: 1
    Last Post: 04-17-2014, 07:05 PM
  3. I need cell to automatically change color based on date in different cell
    By acooley in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-27-2013, 04:55 PM
  4. Replies: 3
    Last Post: 07-08-2010, 06:48 PM
  5. Sorting by Cell Color, Font Color or Cell Icon in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-19-2007, 12:23 PM
  6. Having Cell Color Change Automatically Based on Value
    By jamesfedwards in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-21-2006, 10:47 AM
  7. [SOLVED] Font to change color automatically based on value in cell.
    By mtwelsh72 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2005, 03:06 PM

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