Sign in to follow this  
Followers 0
rameshkumar

_Excel: Color modification to a cell

6 posts in this topic

#1 ·  Posted (edited)

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 by rameshkumar

Share this post


Link to post
Share on other sites



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

Best Regards BugFix  

Share this post


Link to post
Share on other sites

$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

Share this post


Link to post
Share on other sites

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

$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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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:

post-53592-12601695982607_thumb.jpg

Edited by rameshkumar

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0