Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Color a cell (2002/SP3)

    Hi,

    I have a range of number from A1 to F10. I set this range in VBA as:

    Dim r As Range
    Set r = Selection.CurrentRegion

    I want to format the range as follows:
    1) A1 to bold and color 55
    2) B1 to F1 to bold and color 50

    In my code I have:

    r.Cells(1, 1).Font.ColorIndex = 55
    for C = 1 to 5
    r.Cells(1, C).Font.ColorIndex = 50
    next C

    When I run this code nothing happens. Can someone help me here?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Color a cell (2002/SP3)

    Try
    <code>
    With Range("A1").Font
    .Bold = True
    .ColorIndex = 55
    End With
    With Range("B1:F1").Font
    .Bold = True
    .ColorIndex = 50
    End With</code>

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color a cell (2002/SP3)

    I'm looking for a more general form. The statement "Range("A1").Font..." works well for a VBA with a constant range. In my case though the range can change from A1:F10 to another range. That's why I chose to use "CurrentRegion" for the selection. I was reading that using the command "Selection.CurrentRange.Columns(2).Font.Colorindex " works for column 2 in the current range. Is there a general for for picking a single or multiple cells in the selection range and changing the font?


    Any thoughts?

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color a cell (2002/SP3)

    <P ID="edit" class=small>(Edited by wdwells on 01-Nov-08 09:19. Forgot to declare "z", and got my "x" and "y" variables confused, The fallacy of using Air Code.)</P>If you want to make the code relative to the selected range, try:
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Sub</font color=blue> FormatFont()
    <font color=blue>Dim</font color=blue> c <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> x <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> y <font color=blue>As</font color=blue> Long
    Dim z As Long

    x = Selection.Column
    y = Selection.Row
    z = Selection.Columns.Count

    <font color=blue>With</font color=blue> Cells(y, x).Font
    .ColorIndex = 55
    .Bold = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    <font color=blue>For</font color=blue> c = 1 + x <font color=blue>To</font color=blue> x + z - 1
    <font color=blue>With</font color=blue> Cells(y, c).Font
    .ColorIndex = 50
    .Bold = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    <font color=blue>Next</font color=blue> c
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Regards
    Don

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Color a cell (2002/SP3)

    Your code works fine for me as does this version:
    <pre>Dim r As Range
    Set r = Selection.CurrentRegion
    r.Cells(1, 1).Font.ColorIndex = 55
    r.Cells(1, 2).Resize(, 5).Font.ColorIndex = 50
    </pre>


    In what way does yours not work?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color a cell (2002/SP3)

    Thanks Don and Rory. Great help. The formatting code works well now.

    What does "Resize(, 5)" do?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Color a cell (2002/SP3)

    Resize(rowcount, columncount) takes a range and resizes it (starting from the upper left corner) to have the specified number of rows and columns; if either of the dimensions is omitted, it is left unchanged.

    r.Cells(1, 2) is second cell in the top row of the range r.
    .Resize(, 5) leaves the row count unchanged (at 1) and increases the column count to 5 (from 1)
    So r.Cells(1, 2).Resize(, 5) is a range one row high and 5 columns wide starting ar the second cell in the top row of range r.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color a cell (2002/SP3)

    Hi Rory
    Thanks for the Resize command. That's new to me.

    Irrespective of the selected range, your code formats A1:F1 on my 2003 system. Any thoughts on why?
    Regards
    Don

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Color a cell (2002/SP3)

    Works fine for me in 2003. I'd have to guess that the CurrentRegion is extending up to A1 for some reason.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Color a cell (2002/SP3)

    The range r is set to Selection.CurrentRegion. Range.CurrentRegion is the largest rectangular area without empty rows or columns that contains Range. So if the current region of the selection contains A1, the code colors cells A1 and B1:F1.

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color a cell (2002/SP3)

    <hr>I'd have to guess that the CurrentRegion is extending up to A1 for some reason<hr>
    Yes Rory; that was indeed the case. I was unclear of the difference between Selection and region. Hans has sorted this out for me below.
    Regards
    Don

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color a cell (2002/SP3)

    Thank you Hans

    I needed that. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards
    Don

Posting Permissions

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