Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of Shape based on condition - Macro?
I need to have an auto shape, just a regular rectangle, change color based on
a cell next to it. The cell next to it contains an IF statement to either put a 1 or a 0 depending on another sheet. I would like the button color to be grey 25% if the if statement result is 0 and light green if the if statement result is 1. Does anyone know how this could work? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of Shape based on condition - Macro?
The following code will get you started. You'll have to change the RGB
colors to get the colors you want. Put this code in the ThisWorkbook code module. Private Sub Workbook_SheetCalculate(ByVal SH As Object) Dim Rng As Range Dim ShapeName As String Dim SHP As Shape ''''''''''''''''''''''''''''' ' Change the shape name ' to your shape's name. ''''''''''''''''''''''''''''' ShapeName = "Rectangle 1" ''''''''''''''''''''''''''''' ' Change this range to the ' appropriate sheet and cell. ''''''''''''''''''''''''''''' Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("F9") Set SHP = Rng.Parent.Shapes(ShapeName) If Rng.Value = 0 Then SHP.Fill.ForeColor.RGB = RGB(255, 0, 0) Else SHP.Fill.ForeColor.RGB = RGB(0, 255, 0) ' green End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "WBTKbeezy" wrote in message ... I need to have an auto shape, just a regular rectangle, change color based on a cell next to it. The cell next to it contains an IF statement to either put a 1 or a 0 depending on another sheet. I would like the button color to be grey 25% if the if statement result is 0 and light green if the if statement result is 1. Does anyone know how this could work? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of Shape based on condition - Macro?
Chip -
This works perfectly! I have a follow up though... Is there an easier way if I have 24 rectangles to change color based on 24 different cells to do a code instead of copying code 24 times? "Chip Pearson" wrote: The following code will get you started. You'll have to change the RGB colors to get the colors you want. Put this code in the ThisWorkbook code module. Private Sub Workbook_SheetCalculate(ByVal SH As Object) Dim Rng As Range Dim ShapeName As String Dim SHP As Shape ''''''''''''''''''''''''''''' ' Change the shape name ' to your shape's name. ''''''''''''''''''''''''''''' ShapeName = "Rectangle 1" ''''''''''''''''''''''''''''' ' Change this range to the ' appropriate sheet and cell. ''''''''''''''''''''''''''''' Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("F9") Set SHP = Rng.Parent.Shapes(ShapeName) If Rng.Value = 0 Then SHP.Fill.ForeColor.RGB = RGB(255, 0, 0) Else SHP.Fill.ForeColor.RGB = RGB(0, 255, 0) ' green End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "WBTKbeezy" wrote in message ... I need to have an auto shape, just a regular rectangle, change color based on a cell next to it. The cell next to it contains an IF statement to either put a 1 or a 0 depending on another sheet. I would like the button color to be grey 25% if the if statement result is 0 and light green if the if statement result is 1. Does anyone know how this could work? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of Shape based on condition - Macro?
IF the cell that controls the shape's color is the Top Left Cell of the
shape, you could use code like the following. As before, adjust the RGB values to the colors you want. Private Sub Workbook_SheetCalculate(ByVal SH As Object) Dim SHP As Shape Dim TLC As Range For Each SHP In SH.Shapes Set TLC = SHP.TopLeftCell If TLC.Value = 0 Then SHP.Fill.ForeColor.RGB = RGB(255, 0, 0) 'red Else SHP.Fill.ForeColor.RGB = RGB(0, 255, 0) 'green End If Next SHP End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "WBTKbeezy" wrote in message ... Chip - This works perfectly! I have a follow up though... Is there an easier way if I have 24 rectangles to change color based on 24 different cells to do a code instead of copying code 24 times? "Chip Pearson" wrote: The following code will get you started. You'll have to change the RGB colors to get the colors you want. Put this code in the ThisWorkbook code module. Private Sub Workbook_SheetCalculate(ByVal SH As Object) Dim Rng As Range Dim ShapeName As String Dim SHP As Shape ''''''''''''''''''''''''''''' ' Change the shape name ' to your shape's name. ''''''''''''''''''''''''''''' ShapeName = "Rectangle 1" ''''''''''''''''''''''''''''' ' Change this range to the ' appropriate sheet and cell. ''''''''''''''''''''''''''''' Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("F9") Set SHP = Rng.Parent.Shapes(ShapeName) If Rng.Value = 0 Then SHP.Fill.ForeColor.RGB = RGB(255, 0, 0) Else SHP.Fill.ForeColor.RGB = RGB(0, 255, 0) ' green End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "WBTKbeezy" wrote in message ... I need to have an auto shape, just a regular rectangle, change color based on a cell next to it. The cell next to it contains an IF statement to either put a 1 or a 0 depending on another sheet. I would like the button color to be grey 25% if the if statement result is 0 and light green if the if statement result is 1. Does anyone know how this could work? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change line chart color based on condition like + and -. | Charts and Charting in Excel | |||
how to change a cell color based on its content using macro? | New Users to Excel | |||
Change row color based on condition of celss | Excel Worksheet Functions | |||
change shape by condition | Excel Discussion (Misc queries) | |||
Color Change in chart based of condition | Charts and Charting in Excel |