Color Specific Cells in Excel

[vinodchinthakin]

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

[ewilson]

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

1. Create a new action on the VBO and name it something like Set Background Color of Range.
2. Define the following inputs to the Start stage:
   
3. Add a Code stage to the action and name it as you see fit.
4. Add the following Inputs to the Code stage:
   
5. 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
------------------------------

[MichaelAnnis1]

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

[vinodchinthakin]

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

[MichaelAnnis1]

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