Click here to Skip to main content
15,883,999 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,

i want to set the background of excel cell using javascript i try this code but its not working
JavaScript
1)excelSheet.cell(yval + 1, j + 1).Font.Color = "red";
2)excelSheet.Cells(i + 1, 1).Interior.colorindex = "red";
Posted

No, that wont work, since .ColorIndex is expecting a number, rather than text.

If I simply record a macro of me setting the bkg colour of a cell, I get the following code:
VB
Sub colorBkg()
'
' colorBkg Macro
'
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub


This is for the darker of the 2 reds available in 'standard-colors' of Excel2007 (rgb(192,0,0))

The method for computing the number used for Interior.Color is:

Red + (256*green) + (65536*green)

With that in mind, consider the following modification to the recorded macro which sets the colour of any selected cells to rgb(100,100,100)

VB
Sub colorBkg()
'
' colorBkg Macro
'
'
    Dim r, g, b
    r = 100
    g = 100
    b = 100
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = ((b * 65536) + (g * 256) + r)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub


So, I would expect that you'd need to change your code a little. If I use the object browser, I get the following values for the constants:

Const xlAutomatic = -4105 (&HFFFFEFF7)
Const xlSolid = 1

You don't appear to need to set the last 2 attributes, so you could probably get away with:

JavaScript
excelSheet.Cells(i + 1, 1).Interior.pattern = 1;
excelSheet.Cells(i + 1, 1).Interior.PatternColorIndex = -4105;
excelSheet.Cells(i + 1, 1).Interior.color = 255;


Note: I cant remember if the property names are Case-Sensitive from javascript or not.
 
Share this answer
 
Comments
kumar9avinash 5-Apr-14 5:19am    
hi just one thing i want to know ,that i am extracting my color code from html table that is like this (#edc9af) then assignig to excel cell,how to calcluate this #edc9af using formula
enhzflep 5-Apr-14 16:53pm    
In that case, you have the hex representation of each of the three components, 'glued' together. The best way I've found is to chop that value up into 3 pairs of 2 characters, before converting each one back into a number.

Here's a function I've used for going the other way - i,e from an cell-colour to an html one.
// returns the cells background colour as a html hex color value - e.g "7a554a"
this.getCellColor = function(column, row)
{
var hexStrVal, r, g, b, decNum;

decNum = this.mExcelSheet.Cells(row, column).Interior.Color;
// get a hexidecimal string representation of the number
hexStrVal = decNum.toString(16);

// pad to 6 bytes long
while (hexStrVal.length < 6)
{
hexStrVal = "0" + hexStrVal;
}

// extract the 3 components
r = hexStrVal.substr(4,2);
g = hexStrVal.substr(2,2);
b = hexStrVal.substr(0,2);

return r+g+b; // return them in reverse order
}


I guess you'd use the .substr method for cutting into 3 pieces, then you use parseInt to turn from hex digits back into a decimal form, ready for performing the calculation with. parseInt will take something like "0x10" and return 16 - the key point is that you have to have the "0x" prefix.

Soo,

1 - strip the leading hash (#) symbol.
2 - chop up into 3, 2 character pieces
3 - append a "0x" to the start of each string and call parseInt on it
4 - perform the multiplication with the decimal version of the R,G,B values
5 - set the interior.color property with the result of step 5.
JavaScript
var xls = new ActiveXObject("Excel.Application") ;

//set the font color of entire row 
xls.ActiveCell.EntireRow.Font.ColorIndex = 2;

//select the row i.e rangRow here
xls.range(rangRow).Select; 
xls.Selection.Interior.ColorIndex=5


let me know if it resolves your problem or got any idea
 
Share this answer
 
Comments
kumar9avinash 5-Apr-14 5:03am    
what is rangerow here
kumar9avinash 5-Apr-14 5:04am    
if i am giving(i + 1, 1) instead of rangerow it throws error saying ";" expected
Suk@nta 5-Apr-14 7:55am    
here you can provide row index only

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900