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.