_Excel: Color modification to a cell

[rameshkumar]

Hi,

I prepared a script which will read processes information to an excel sheet.

My problem here is, from that excel sheet, I want to set color to a specific cell which meets the requirements.

But here it is possible to do font settings like BOLD, UNDERLINE, ITALIC only.

Can any one help me in how to set different colors to cells in the excel sheet read?

Edited December 6, 2009 by rameshkumar

[BugFix]

Here an example:

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("E1:J1")  .Font.Bold = TRUE
    .Range("I2:J11") .Font.Bold = TRUE
    .Range("E11:J11").Font.Bold = TRUE
    .Range("E1:J1")  .Interior.Color = 0xCCFFCC
    .Range("A2:A10") .Interior.Color = 0xCCFFFF
    .Range("A3")     .Interior.Color = 0xFFFF99
    .Range("A5")     .Interior.Color = 0xFFFF99
    .Range("A7")     .Interior.Color = 0xFFFF99
    .Range("A9")     .Interior.Color = 0xFFFF99
    .Range("E2:J10") .Interior.Color = 0xCCFFFF
    .Range("E3:J3")  .Interior.Color = 0xFFFF99
    .Range("E5:J5")  .Interior.Color = 0xFFFF99
    .Range("E7:J7")  .Interior.Color = 0xFFFF99
    .Range("E9:J9")  .Interior.Color = 0xFFFF99
    .Range("E11:J11").Interior.Color = 0xCC99FF
EndWith

[picaxe]

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
With $oExcel ; open new workbook
    .Visible = True
    .WorkBooks.Add
    .ActiveWorkbook.Sheets(1).Select()
EndWith

$sRange ="A1:G8"
$k = 1
For $i = 1 To 7
    For $j = 1 To 8
        $oExcel.ActiveSheet.Range(Chr(64 + $i) & $j).Value = "ColorIndex: " & $k
        With $oExcel.ActiveSheet.Range(Chr(64 + $i) & $j).Font
            If Mod($j, 2) Then
                .Name = "Courier New"
                .FontStyle = "Italic"
                ;.Underline = 2
            Else
                .Name = "Comic Sans MS"
                .FontStyle = "Regular"
                .Underline = -4119  ; double underline
                ;.Underline = 4 ; accounting single underline
                ;.Underline = 5 ; accounting double underline
            EndIf
            .Bold = True
            .ColorIndex = $k
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
        EndWith
        $k += 1
    Next
Next
; format cells
With $oExcel.ActiveSheet.Range($sRange)
    .Select
    .HorizontalAlignment = -4108 ; center
    .ColumnWidth = 18
    ;.EntireColumn.AutoFit
EndWith
; color columns
For $i = 1 To 7
    $sCol = Chr(64 + $i)
    If Mod($i, 2) Then
        $oExcel.ActiveSheet.Range($sCol & "1:" & $sCol & "8").Interior.ColorIndex = 3
    Else
        $oExcel.ActiveSheet.Range($sCol & "1:" & $sCol & "8").Interior.ColorIndex = 20
    EndIf
Next

[BugFix]

By using ColorIndex it's interesting to know, which colors are reflected.

Here you can get an overview: http://www.mvps.org/dmcritchie/excel/colors.htm

[rameshkumar]

Thanks dude. It really helps me a lot. Thanks again.

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
With $oExcel ; open new workbook
    .Visible = True
    .WorkBooks.Add
    .ActiveWorkbook.Sheets(1).Select()
EndWith

$sRange ="A1:G8"
$k = 1
For $i = 1 To 7
    For $j = 1 To 8
        $oExcel.ActiveSheet.Range(Chr(64 + $i) & $j).Value = "ColorIndex: " & $k
        With $oExcel.ActiveSheet.Range(Chr(64 + $i) & $j).Font
            If Mod($j, 2) Then
                .Name = "Courier New"
                .FontStyle = "Italic"
                ;.Underline = 2
            Else
                .Name = "Comic Sans MS"
                .FontStyle = "Regular"
                .Underline = -4119  ; double underline
                ;.Underline = 4 ; accounting single underline
                ;.Underline = 5 ; accounting double underline
            EndIf
            .Bold = True
            .ColorIndex = $k
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
        EndWith
        $k += 1
    Next
Next
; format cells
With $oExcel.ActiveSheet.Range($sRange)
    .Select
    .HorizontalAlignment = -4108 ; center
    .ColumnWidth = 18
    ;.EntireColumn.AutoFit
EndWith
; color columns
For $i = 1 To 7
    $sCol = Chr(64 + $i)
    If Mod($i, 2) Then
        $oExcel.ActiveSheet.Range($sCol & "1:" & $sCol & "8").Interior.ColorIndex = 3
    Else
        $oExcel.ActiveSheet.Range($sCol & "1:" & $sCol & "8").Interior.ColorIndex = 20
    EndIf
Next

[rameshkumar]

Detailed clarification to add colors:

$cRow ; represents row

$cCol ; represents column

$cColorPallet ; represents color pallet number.

$sCol = Chr(64 + $cCol)

$oExcel.ActiveSheet.Range($sCol & $cRow).Interior.ColorIndex = $cColorPallet

Color Pallets:

Edited December 7, 2009 by rameshkumar