Excel Cell Color identification

vinay

Member
Hi, Can anyone tell me how to identify color of the cell in excel and how to fill the color to the cell?
Thank you
 

VJR

Well-Known Member
Hi vinay,

How to set the FillColor to a cell:
You can make a copy of the existing 'Format Cell' action of the MS Excel VBO.
Remove the existing code and add the below line to the Code stage.

Code:
GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref,cellref).Interior.Color = RGB(Red_Value, Green_Value, Blue_Value)

So the Start stage parameters of this new action are handle, Cell Reference and the Red, Green, Blue numeric constants.
All these parameters will also be passed to the Input parameters of the Code stage.

You can get the RGB constants by clicking on a cell that already has a color and then from the Fill Color button > More Colors > Custom tab, at the bottom you will find the RGB color constants.

1524228143958.png

The above screenshot is the output after running the process.

From the process you can also pass a range of cells to the Cell Reference parameter like "A1:A10". This will color all the cells from A1 to A10 with the same passed color constants.

How to get the FillColor of a cell:

You can make a copy of the above action that you just created.
The Start stage parameters of the action will be only handle and Cell Reference. Pass the same to the Code stage inputs.
The End stage parameters of the new action will be numeric values for Red, Green, Blue and another value called as LongConstant (explained later). The 4 numeric values will also be the Output parameters of the Code stage.

Add the below code to the Code stage-

Code:
   LongConstant = GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref).Interior.Color

   Red_Value = LongConstant And 255
   Green_Value = LongConstant \ 256 And 255
   Blue_Value = LongConstant \ 256 ^ 2 And 255

The '.Interior.Color' method of a cell returns a long numeric constant. This constant is a mathematical calculation of the corresponding RGB combination. This number is not easy to interpret what color it is hence there is a need to break it down into its corresponding RGB values as calculated above.

Unlike the set FillColor process you cannot pass a range of cells since it is not feasible to get all the different colors of a wide range of cells without using something like an array.

The output after running this new action from a process will be as below-

1524229132243.png

- A LongConstant is returned along with its split values for Red, Green and Blue constants.
The reason for retrieving the LongConstant is that one can very well write a code to set the FillColor as below-
Code:
GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref,cellref).Interior.Color = 16575868
But it is not easy to understand this constant and so using the RGB function is a better practice. This parameter can be skipped if it is not required.

- You can also notice that the values of the RGB constants in the data items are the same as shown in the first screenshot above.
 

vinay

Member
Hi vinay,

How to set the FillColor to a cell:
You can make a copy of the existing 'Format Cell' action of the MS Excel VBO.
Remove the existing code and add the below line to the Code stage.

Code:
GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref,cellref).Interior.Color = RGB(Red_Value, Green_Value, Blue_Value)

So the Start stage parameters of this new action are handle, Cell Reference and the Red, Green, Blue numeric constants.
All these parameters will also be passed to the Input parameters of the Code stage.

You can get the RGB constants by clicking on a cell that already has a color and then from the Fill Color button > More Colors > Custom tab, at the bottom you will find the RGB color constants.

View attachment 612

The above screenshot is the output after running the process.

From the process you can also pass a range of cells to the Cell Reference parameter like "A1:A10". This will color all the cells from A1 to A10 with the same passed color constants.

How to get the FillColor of a cell:

You can make a copy of the above action that you just created.
The Start stage parameters of the action will be only handle and Cell Reference. Pass the same to the Code stage inputs.
The End stage parameters of the new action will be numeric values for Red, Green, Blue and another value called as LongConstant (explained later). The 4 numeric values will also be the Output parameters of the Code stage.

Add the below code to the Code stage-

Code:
   LongConstant = GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref).Interior.Color

   Red_Value = LongConstant And 255
   Green_Value = LongConstant \ 256 And 255
   Blue_Value = LongConstant \ 256 ^ 2 And 255

The '.Interior.Color' method of a cell returns a long numeric constant. This constant is a mathematical calculation of the corresponding RGB combination. This number is not easy to interpret what color it is hence there is a need to break it down into its corresponding RGB values as calculated above.

Unlike the set FillColor process you cannot pass a range of cells since it is not feasible to get all the different colors of a wide range of cells without using something like an array.

The output after running this new action from a process will be as below-

View attachment 613

- A LongConstant is returned along with its split values for Red, Green and Blue constants.
The reason for retrieving the LongConstant is that one can very well write a code to set the FillColor as below-
Code:
GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref,cellref).Interior.Color = 16575868
But it is not easy to understand this constant and so using the RGB function is a better practice. This parameter can be skipped if it is not required.

- You can also notice that the values of the RGB constants in the data items are the same as shown in the first screenshot above.
Thank You for your response
 

rpa_noob

New Member
I tried the above steps and am getting the error "The given key was not present in the dictionary" when executing the code stage. Could you please tell me what i am doing wrong?
 

Madala

New Member
You might be using different excel objects for excel operation in process.Try using single excel object in a process


I tried the above steps and am getting the error "The given key was not present in the dictionary" when executing the code stage. Could you please tell me what i am doing wrong?
 

marcelopbg94

New Member
Hi!
Actually the MS Excel VBO does provide a way to change the text color of a cell like below, indeed, for the background you need to follow the other steps presented above.
main-qimg-6a34a57e239b1a67b85e96f5b05ddd11

Refer to this link for more custom formatting options:
Appendix B: Using Custom Number Formats
 
Top