topic RE: Color Specific Cells in Excel in Product Forum

Color Specific Cells in Excel

vinodchinthakin — Tue, 08 Nov 2022 10:02:00 GMT

Hi All,

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
------------------------------

RE: Color Specific Cells in Excel

ewilson — Tue, 08 Nov 2022 12:46:00 GMT

@vinod chinthakindi,

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
------------------------------

RE: Color Specific Cells in Excel

MichaelAnnis1 — Tue, 08 Nov 2022 15:48:00 GMT

You should be able to access conditional formatting. Even if it does not already exist in the Excel VBO, you could use application modeler to get the UI's needed for conditional formatting.

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
------------------------------

RE: Color Specific Cells in Excel

vinodchinthakin — Tue, 08 Nov 2022 16:06:00 GMT

Thanks Michael. Definitely It can help me.
But, I am looking for a VBA code for a single go. As am not much good at coding.

------------------------------
vinod chinthakindi
------------------------------

RE: Color Specific Cells in Excel

MichaelAnnis1 — Tue, 08 Nov 2022 18:29:00 GMT

Below...just change the column selection in Bold. Good luck!

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
------------------------------

Re: RE: Color Specific Cells in Excel

Omar-Abu-Snineh — Sat, 22 Mar 2025 10:26:39 GMT

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

Re: RE: Color Specific Cells in Excel

Omar-Abu-Snineh — Sun, 23 Mar 2025 19:36:12 GMT

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.

Re: Color Specific Cells in Excel

naveed_raza — Mon, 01 Dec 2025 11:14:37 GMT

here is the Excel VBA Code , if you want to use

Sub colorYesValue() Dim Wks As Worksheet Set Wks = ThisWorkbook.Sheets("Sheet1") 'if you want to run on active workbook from macro file then you can enter ActiveWorkbook.Sheet("SheetName") Wks.AutoFilterMode = False Dim Lr As Long Lr = Wks.Range("A1").CurrentRegion.Rows.Count Dim hdrRng As Range Set hdrRng = Wks.Range("1:1").Find("Result", , , xlWhole) If Lr > 1 And Not hdrRng Is Nothing Then Wks.Range("A1").CurrentRegion.AutoFilter hdrRng.Column, "Yes" If (Wks.AutoFilter.Range.Columns("A").SpecialCells(12).Cells.Count - 1) > 0 Then Wks.Range(Wks.Cells(2, hdrRng.Column).Address & ":" & Wks.Cells(Lr, hdrRng.Column).Address).SpecialCells(12).Interior.Color = vbRed End If Wks.AutoFilterMode = False End If Set hdrRng = Nothing End Sub