Color a cell (2002/SP3)

Topic

New Reply

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?

Reply | Quote

Replies

Try

With Range("A1").Font
.Bold = True
.ColorIndex = 55
End With
With Range("B1:F1").Font
.Bold = True
.ColorIndex = 50
End With

Reply | Quote

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?

Reply | Quote

(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.)

If you want to make the code relative to the selected range, try:

Sub FormatFont()
Dim c As Long
Dim x As Long
Dim y As Long
Dim z As Long

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

With Cells(y, x).Font
.ColorIndex = 55
.Bold = True
End With
For c = 1 + x To x + z - 1
With Cells(y, c).Font
.ColorIndex = 50
.Bold = True
End With
Next c
End Sub


Reply | Quote

Your code works fine for me as does this version:

Dim r As Range
Set r = Selection.CurrentRegion
r.Cells(1, 1).Font.ColorIndex = 55
r.Cells(1, 2).Resize(, 5).Font.ColorIndex = 50

In what way does yours not work?

Reply | Quote

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

What does “Resize(, 5)” do?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

[indent]

---

I’d have to guess that the CurrentRegion is extending up to A1 for some reason

---

[/indent] 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.

Reply | Quote

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.

Reply | Quote

Thank you Hans

I needed that.

Reply | Quote