Can't set font color. Bug?

Question

I'm doing it like this:

 

				range.Font.ThemeColor = XlThemeColor.xlThemeColorDark1;
				range.Font.TintAndShade = -0.5;

However, font color becomes white (not gray, as intended). And TintAndShade is zero immediately after assignment. Ideas?

 

UPD: Office 2010, all latest updates installed

Answer 1

I can confirm the bug. The following constants are defined in the type library:

Const xlThemeColorDark1 = 1
Const xlThemeColorLight1 = 2
Const xlThemeColorDark2 = 3
Const xlThemeColorLight2 = 4
Const xlThemeColorAccent1 = 5
Const xlThemeColorAccent2 = 6
Const xlThemeColorAccent3 = 7
Const xlThemeColorAccent4 = 8
Const xlThemeColorAccent5 = 9
Const xlThemeColorAccent6 = 10
Const xlThemeColorHyperlink = 11
Const xlThemeColorFollowedHyperlink = 12

The following assignment works as expected:

Const xlThemeColorLight1 = 1
Const xlThemeColorDark1 = 2
Const xlThemeColorLight2 = 3
Const xlThemeColorDark2 = 4

TinteAndShade only works for shapes:

ActiveSheet.Shapes(1).TextFrame.Characters.Font.TintAndShade = -0.5

Answer 2

Here is an alternative if you are interested.

 

 private void button1_Click(object sender, EventArgs e)
 {
  Excel.Application xlexcel;
  Excel.Workbook xlWorkBook;
  Excel.Worksheet xlWorkSheet;
  Excel.Range rng;

  object misValue = System.Reflection.Missing.Value;
  xlexcel = new Excel.Application();
  xlexcel.Visible = true;
  xlWorkBook = xlexcel.Workbooks.Add(misValue);
  xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
  rng=xlWorkSheet.get_Range("A1",misValue );
  rng.Value = "Sample text";
  rng.Font.ColorIndex = 16;
 }

Edit

You may also experiment with other shades of Grey here :)

Topic: Excel Color Palette and Color Index change using VBA

Link: http://www.databison.com/index.php/excel-color-palette-and-color-index-change-using-vba/

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 3

Try xlThemeColorLight1 instead.

The constant names for Light & Dark are reversed in the built-in schemcolor palettes. (Don't ask!)

Peter Thornton

Answer 4

I'm seeing the same as user4756. TintAndShade remains zero after assigning it a value.

I'm working on a solution that needs to correctly restore formats to a sheet so being able to set the tintandshade property is essential.

Answer 5

Just as a follow up. I believe this code proves that this is an Excel Bug. The code *should* produce text that changes font colour by row in the same manner the fill colour is changing but the text remains a constant shade.

 

Sub test_color()
 'Fills Work
 For i = 1 To 256
 Cells(i, 1).Interior.ThemeColor = xlThemeColorAccent1
 Cells(i, 1).Interior.TintAndShade = (i * 2 - 256) / 256
 Next i

 'Fonts Don't
 For i = 1 To 256
 Cells(i, 2).Value = "Broken"
 Cells(i, 2).Font.ThemeColor = xlThemeColorAccent1
 Cells(i, 2).Font.TintAndShade = (i * 2 - 256) / 256
 Next i
End Sub

Answer 6

Your example works fine for me in 2007. Exactly same RGB for fill in colA and font in ColB, from dark blue in row1 to light blue in row256, exactly as expected with the default Office color-scheme.

Peter Thornton

Answer 7

Thanks for testing it Peter.

I now suspect this bug only affects Excel 2010 (I'm on 2010). Could someone running a fully patched version of 2010 confirm whether they are seeing the same issue by running the above test_color routine?

Answer 8

macky_

It is a bug with Excel 2010. And hence I suggested an alternative in my previous post.

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 9

It is a bug with Excel 2010. And hence I suggested an alternative in my previous post.

So it is! Generally 2010 fixes the bugs from 2007, but not in this case it seems

Here's another workaround, apply and reset the themecolor / tintandshade combo fill in a dummy cell, grab the RGB (and reset when done)

Sub test_color2()
 'Fills Work
 For i = 1 To 256
 Cells(i, 1).Interior.ThemeColor = xlThemeColorAccent1
 Cells(i, 1).Interior.TintAndShade = (i * 2 - 256) / 256
 Next i
 'Fonts Don't but do now
 For i = 1 To 256
 Cells(i, 2).Value = "Fixed"
 Cells(i, 2).Font.Color = Cells(i, 1).Interior.Color
 Next i
End Sub

Note if user applies a different Color-scheme these font colours will not update

Peter Thornton

Answer 10

The work around would help most but, unfortunately, not me becuase I need to 100% replicate the desired formatting, including the theme characteristics.

Is there anyway of notifying MSFT? This is a pretty fundamental bug - font formatting via vba is broken. Do they monitor these forums?

Answer 11

> Is there anyway of notifying MSFT?

I've reported it.

In a light search this issue only seems to have cropped up recently, wonder if it's only since an update.

Peter Thornton

Answer 12

Justed tested my Excel 2013 Preview and the bug has been fixed. My fully patched Excel 2010 still exhibits the bug... 

Answer 13

Lets assume they picked up on the bug report, for 2013 at least!

Peter Thornton