Jump to content
Sign in to follow this  
rameshkumar

_Excel: Color modification to a cell

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Best Regards BugFix  

Share this post


Link to post
Share on other sites
picaxe

$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

  • Like 1

Share this post


Link to post
Share on other sites
rameshkumar

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
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:

post-53592-12601695982607_thumb.jpg

Edited by rameshkumar
  • Like 1

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.