Jul 19 2023 03:46 PM
Jul 19 2023 03:46 PM
Very new to excel with only a basic understanding. I have a sheet where I have numerous shapes (freeform: shape), and a table of cells in which each cell represents one of the shapes. These cells change colour and text from drop down lists. I want to be able to make the outline of the shape change colour based on what the cell value is. For example if the cell for shape 1 is colored green, and says "Normal", I want the outline of shape 1 to automatically turn green. I have looked at a few online tutorials and searched around, but struggling to make anything work. Any ideas greatly appreciated!
Jul 20 2023 12:12 AM
To achieve the desired functionality where the outline color of a shape changes based on the corresponding cell's value and color in Excel, you can use VBA (Visual Basic for Applications) to create a macro. The following steps will guide you through the process:
Step 1: Assign Names to Shapes and Cells
Step 2: Create a VBA Macro
Private Sub Worksheet_Change(ByVal Target As Range) Dim shp As Shape On Error Resume Next If Target.Count = 1 Then ' Check if the changed cell has a shape associated with it Set shp = Me.Shapes.Item(Target.Value & "_ColoredCell") ' If the shape is found, update its outline color If Not shp Is Nothing Then Select Case Target.Value Case "Normal" shp.Line.ForeColor.RGB = RGB(0, 255, 0) ' Green Case "Warning" shp.Line.ForeColor.RGB = RGB(255, 255, 0) ' Yellow Case "Error" shp.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red Case Else ' Set a default color if the cell value doesn't match any case shp.Line.ForeColor.RGB = RGB(0, 0, 0) ' Black End Select End If End If End Sub
Step 3: Save and Close the VBA Editor Save the workbook as a macro-enabled workbook (.xlsm) to retain the VBA code.
Now, when you change the value in the cells corresponding to each shape, the outline color of the corresponding shape should change based on the cell's value. The code includes cases for "Normal", "Warning", and "Error", but you can customize it according to your specific needs.
Please note that this code assumes you have already set up the shapes and corresponding cells as described in Step 1. Additionally, the code should be placed in the worksheet module where you have the shapes and cells, not in a standard module. The text and steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Jul 20 2023 05:18 AM
You can use VBA to achieve your goal.
Here is how you can do it (it is a concept, I haven't tested it)
1. Open the Visual Basic for Applications (VBA) Editor:
- Press "ALT + F11" in Excel to open the VBA Editor.
2. Insert a new module:
- In the VBA Editor, click on "Insert" in the menu, then click "Module."
3. Paste the VBA code:
- Copy and paste the following VBA code into the module you created:
Sub ChangeShapeOutlineColor() Dim ws As Worksheet Dim shapeCell As Range Dim shape As Shape Dim cellColor As Long 'Specify the worksheet containing the shapes and the table with cell colors Set ws = ThisWorkbook.Worksheets("Sheet1") 'Loop through each shape For Each shape In ws.Shapes 'Get the corresponding cell based on the shape name Set shapeCell = ws.Range("A:A").Find(shape.Name) If Not shapeCell Is Nothing Then 'Get the color of the corresponding cell cellColor = shapeCell.Interior.Color 'Change the shape outline color to match the cell color shape.Line.ForeColor.RGB = cellColor End If Next shape End Sub
4. Run the macro:
- Close the VBA Editor and return to your Excel sheet.
- Press "ALT + F8" to open the "Macro" dialog box.
- Select the "ChangeShapeOutlineColor" macro from the list and click "Run."
The VBA macro loops through all shapes on the specified worksheet ("Sheet1" in this example). It matches each shape's name with the content in column A to find the corresponding cell. Then, it retrieves the cell's interior color and applies it to the shape's outline color
You can check these Microsoft Learn links if you want to learn some more about VBA and macros in Excel:
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.
If the post was useful in other ways, please consider giving it Like.