Color Specific Cells in Excel
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-11-22 10:02 AM
I would like to color cells in Excel for specified cells.
Let's say , Excel has a Column "Result" consists of values Yes and No.
I want to color all cells with Red whose cell values are No similarly Green if cell value is Yes
Do we have any customized code?
------------------------------
vinod chinthakindi
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-11-22 12:46 PM
I don't believe there's an existing action on the MS Excel VBO that supports this, but it's easy enough to add one.
- Create a new action on the VBO and name it something like Set Background Color of Range.
- Define the following inputs to the Start stage:
- Add a Code stage to the action and name it as you see fit.
- Add the following Inputs to the Code stage:
- Add this code to the Code stage:
Dim range As Object
Dim currentCell As Object
Try
range = GetWorkbook(handle,Nothing).ActiveSheet.Range(startcell,endcell)
For Each currentCell In range
currentCell.Interior.Color = RGB(color_R, color_G, color_B)
Next currentCell
Catch ex As Exception
Finally
range = Nothing
End Try
Make sure to save your changes.
The above will give you a general action you can use to the set the background color of cells in a specific range. The color is based on RGB (Red, Green, Blue) values, so to set a cells background to red you would set ColorValue-R = 255 and the others to 0. Hopefully that makes sense.
This action doesn't account for the value criteria you mentioned though. You could either extend the code above the include a check of the cells value before changing the color, or you could just use Find action of the VBO to find all cells that have a value of Yes (or No) and use then iterate over the resulting Collection and call this new action on each of those cells.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-11-22 03:48 PM
If you know VBA, you may be able to create a VBA macro and run that too, instead of capturing all these UI elements.
Select the Column
Go to Home -> Conditional Formatting
Select Highlight Cells -> Text that Contains
Type in Yes, click the dropdown to Custom
Select Fill, your Green color, okay.
Repeat all the steps for "No" and Red color.
Best of luck!
------------------------------
Michael Annis
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-11-22 04:06 PM
But, I am looking for a VBA code for a single go. As am not much good at coding.
------------------------------
vinod chinthakindi
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-11-22 06:29 PM
Sub Yes_Green_No_Red()
'
' Yes_Green_No_Red Macro
'
'
Columns("A:A").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Yes", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="No", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13421823
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
------------------------------
Michael Annis
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
22-03-25 10:26 AM
Mr. Ewilson thanks a lot, I've tried this method, and it works perfectly. However, I'm wondering if any actions have been developed in the newer versions of MS Excel VBO? in 2025
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-03-25 07:36 PM
After conducting some research, it appears that there is an enhanced version of the MS Excel VBO. Instead of using the standard one, you should use the MS Excel VBO Extended, which you can download from the following link: MS Excel VBO Extended.
