Why isn't this code to change the font colour working?

[travis]

The following snippet of code is supposed to change the value in the
8th column row the row MatchLineNum to "Yes" and then change the first
22 columns of that row to black font.

With Sheets("Macquarie Commissions")
..Cells(MatchLineNum, 8).Value = "Yes"
..Cells(MatchLineNum, 22).Font.ThemeColor = xlThemeColorLight1
End With

The "Yes" part works fine.
The font colour change on the other hand doesn't do anything at all.
The font colour remains the same as it was before the macro was run.

Why?

Travis

 

[Barb Reinhardt]

How about (not sure why you have .. instead of . either)

..Cells(MatchLineNum, 22).Font.Color = xlThemeColorLight1

I'm not sure what xlThemeColorLight1 is, but you can get the font colors
from here.

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

Scroll to the bottom of the page.

 

[JLGWhiz]

Threw me too, Barb. But guess what, it is something in xl2007 developers
guide.
http://msdn.microsoft.com/en-us/library/bb242965.aspx

 

[Barb Reinhardt]

Imagine that, another change.

Thanks for the info. I'm guessing it only works on 2007 though.

Barb Reinhardt

 

[travis]

How about (not sure why you have .. instead of . either)

.Cells(MatchLineNum, 22).Font.Color = xlThemeColorLight1

Still not working (with that, or any other colour). It simply doesn't
change the font colour at all.

I've searched this newsgroup's past answers and it appears a lot of
people had issues with it. I've managed a workaround by using a
select, copy, paste method instead of changing the .font.color
property, but why doesn't the .font.color property work? Is it just
2007, or earlier versions as well?

Travis

 

[Gary Keramidas]

don't know, did you try colorindex instead of color?

--


Gary

How about (not sure why you have .. instead of . either)

.Cells(MatchLineNum, 22).Font.Color = xlThemeColorLight1

Still not working (with that, or any other colour). It simply doesn't
change the font colour at all.

I've searched this newsgroup's past answers and it appears a lot of
people had issues with it. I've managed a workaround by using a
select, copy, paste method instead of changing the .font.color
property, but why doesn't the .font.color property work? Is it just
2007, or earlier versions as well?

Travis

 

[travis]

The following snippet of code is supposed to change the value in the
8th column row the row MatchLineNum to "Yes" and then change the first
22 columns of that row to black font.

With Sheets("Macquarie Commissions")
.Cells(MatchLineNum, 8).Value = "Yes"
.Cells(MatchLineNum, 22).Font.ThemeColor = xlThemeColorLight1
End With

Ugh! I made a dumb noob error!

If you look at my description of what I wanted it to do (change the
first 22 columns in the row to black font) and what I actually codes
(change the 22nd cell only), its obvious why it wasn't working!

This worked though:

With Sheets("Macquarie Commissions")
.Cells(MatchLineNum, 8).Value = "Yes"
.Rows(MatchLineNum).Font.ThemeColor = xlThemeColorLight1
End With

Sorry!

Travis