Results 1 to 6 of 6

Thread: Coloring cells

  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Question

    How do i color cells in an Excel sheet from MS Access?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='patt' post='780005' date='16-Jun-2009 14:21']How do i color cells in an Excel sheet from MS Access?[/quote]
    I found out how to bold a cell and this proved enough for the task.

    objActiveWkb.Worksheets(1).Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior.ColorIndex = 43
    objActiveWkb.Worksheets(1).Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior.Pattern = xlSolid
    objActiveWkb.Worksheets(1).Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior.PatternColorIndex = xlAutomatic

    This colurs cells A1 thru I2 a dark green

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='780007' date='16-Jun-2009 04:23']I found out how to bold a cell and this proved enough for the task.

    objActiveWkb.Worksheets(1).Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior.ColorIndex = 43
    objActiveWkb.Worksheets(1).Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior.Pattern = xlSolid
    objActiveWkb.Worksheets(1).Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior.PatternColorIndex = xlAutomatic

    This colurs cells A1 thru I2 a dark green[/quote]

    Hi Patt

    If 'dark green' is not suitable you can pick another of the default colours by changing the 43, his ite has a list of the available colours

    http://www.mvps.org/dmcritchie/excel/colors.htm
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can probably omit the second and third lines. If not, you can make the code slightly more efficient:

    Code:
    With xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior
      .ColorIndex = 43
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
    or

    Code:
    With xlSheet.Range("A1:I2").Interior
      .ColorIndex = 43
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='stevehocking' post='780012' date='16-Jun-2009 16:56']Hi Patt

    If 'dark green' is not suitable you can pick another of the default colours by changing the 43, his ite has a list of the available colours

    http://www.mvps.org/dmcritchie/excel/colors.htm[/quote]
    Thanks for the link

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='780016' date='16-Jun-2009 17:52']You can probably omit the second and third lines. If not, you can make the code slightly more efficient:

    Code:
    With xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 9)).Interior
      .ColorIndex = 43
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
    or

    Code:
    With xlSheet.Range("A1:I2").Interior
      .ColorIndex = 43
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
    [/quote]
    Thanks for that Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •