Adding cell color

[Ahammad_Riyaz]

Hi all,

I want to can we able to add cell color to excel using Power automation desktop.

Please let me know if anyone knows, In uipath it is possible can it possible in PAD.

Regards,

Ahammad Riyaz

[VJR]

Hi @Ahammad_Riyaz 

No readymade action available in PAD to do that.

One backdoor approach is to keep a cell somehwere far in your sheet which is not visible to the user.

For example in Z1. Fill this cell manually with the color you want.

Then in PAD use "Copy cells" from Z1 

and "Paste cells" into your destination cell.

Tried this, it works.

[Ahammad_Riyaz]

Hi @VJR,

Thanks for your reply,

I was already tried with that method also,

we need to update excel in that excel color is white so if i use as copy cell and paste method also the value it was getting in white color itself.

Any alternate solution ?

[Henrik_M]

Nothing built-in.

You would have to use VBScript.

Here's a link to get you started: https://devblogs.microsoft.com/scripting/how-can-i-assign-a-background-color-to-cells-in-a-spreadshe...

[VJR]

Hi @Ahammad_Riyaz 

In that situation, 

- First read the value of the cell. Store it in a variable

- Then copy paste the color 

- Then write back the value that was stored in the variable

[Highboy]

Can be done by running an powershell script (3 lines)...
This script attaches to an already running excel instance and enables you to manipulate it.
You only need the powershell script, but the script belows is just a demonstration of what it can do.
Code to paste into PAD Below the pictures

****************** Copy all below this line ******************

Excel.LaunchExcel.Launch Visible: True LoadAddInsAndMacros: False Instance=> ExcelInstance
SET Color TO 1
LOOP row FROM 1 TO 7 STEP 1
LOOP Column FROM 1 TO 8 STEP 1
Scripting.RunPowershellScript Script: $'''$xl = [Runtime.Interopservices.Marshal]::GetActiveObject(\'Excel.Application\')
$sheet = $xl.WorkSheets.Item(1)
$sheet.Cells.Item(%row%,%column%).Interior.ColorIndex = %color%''' ScriptOutput=> PowershellOutput ScriptError=> ScriptError
Variables.IncreaseVariable Value: color IncrementValue: 1 IncreasedValue=> color
END
END
Display.ShowMessageDialog.ShowMessage Message: $'''All done''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
Excel.CloseExcel.Close Instance: ExcelInstance

[VJR]

If going by a script option, VBA excel macro is the most suitable because it will directly use the Excel Object Model library (same as the parent application "ie; Excel")

- Write a VBA function that accepts parameters like Workbook name, range, color, sheet, etc

- Just requires 1 or 2 lines of Code to change color (can help with code if necessary).

- Simply pass the parameters from PAD using "Run Excel macro" action  

[Ahammad_Riyaz]

Hi @VJR 

Can u plz send me the steps what should i do.

[VJR]

Hi @Ahammad_Riyaz 

Have you tried this from my above post?

In that situation, 

- First read the value of the cell. Store it in a variable

- Then copy paste the color 

- Then write back the value that was stored in the variable

[Ahammad_Riyaz]

Hi @Highboy 

Can you send me the powershell code

[Highboy]

Just paste the code from my post to you PAD, you can get it from there.

[Ahammad_Riyaz]

Hi @Highboy ,

If i copy paste i was getting unknown error on some actions.

[Highboy]

Did you copy all of it

[MichaelAnnis]

If you want to change the cell by HexKey, you can use Send Keys to navigate through the Ribbon Menu after the cell is selected.  For example, the following Send Keys command will change any selection to white:

SET HexColorCode TO $'''#FFFFFF'''
MouseAndKeyboard.SendKeys TextToSend: $'''{Alt}()hhm{Right}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}%HexColorCode%{Return}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False

This would be dynamic and you can use any color as long as you know the code.

Alternatively, you could use the same logic, if you know the Red/Green/Blue values; this made a purple color:

SET RedValue TO 91
SET GreenValue TO 59
SET BlueValue TO 229
MouseAndKeyboard.SendKeys TextToSend: $'''{Alt}()hhm{Right}{Tab}{Tab}{Tab}{Tab}%RedValue%{Tab}%GreenValue%{Tab}%BlueValue%{Return}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False

[VJR]

Hi @Ahammad_Riyaz 

Can u plz send me the steps what should i do.

In order for me to share exact steps,

Can you share details or screenshots.

i. How many cells would you like to add color to?

ii. Also, are you looking for cell font color Or cell background color?

[Ahammad_Riyaz]

The requirement is i need to create header for the input file, status and comments, in that excel contains white color for cells so  in need to paste some color to that cells while creating headers.

Font color or background color also fine for me for last 2 cells freecolumn-1 nd 2

@MichaelAnnis @VJR @Highboy @Henrik_M 

[MichaelAnnis]

Ok. So do your other processes and inside of the processes, add one of the two flows I made below. All you have to do is change the cell being selected and change the color code (Hex or RGB). I would make those dynamic if you want different cells to be different. 

[VJR]

Hi @Ahammad_Riyaz 

For the requirements you have provided, I dont think a script would be needed.

But I want to confirm before giving you a suggestion.

Can you share the screenshot of how it is currently and what you are looking for, so that I can suggest an easier solution?

[Max04]

Is there a way to filter the excel cells by colour in Flow?