Solved Changing Font Color in Microsoft Excel 2007

Microsoft Microsoft excel 2007 (pc)
August 9, 2011 at 15:13:26
Specs: Windows XP
Can someone help me create a macro or tell me how to accomplish the following? I would like to change the font color of the text in a cell depending on what is in the cell. I do not want to change the entire font color for everything in the cell, just the specific attribute to chage.

For example, when "Microsoft 2012" is in the cell, the font color would be "orange" for 2012, but no change to Microsoft.

I'll have multiple worksheets in a workbook to search and change the font to. We use a lot of dates/years in Excel and would like to isolate to a given year with a particular color so it could be easily recognizeable when looking at the worksheet/workbook. I hope this makes sense.

Thanks in Advance

See More: Changing Font Color in Microsoft Excel 2007

Report •

August 9, 2011 at 20:24:39
✔ Best Answer
There is a major difference between Microsoft 2012 and August 9, 2012

Microsoft 2012 is a Text String while August 9, 2012 is a Date, stored internally by Excel as 5 digit number, specifically 41130. Excel stores Dates and Times as numbers, with the integer being the Date and the decimal portion being the Time. 1/1/1900 is Day 1, 8/9/2012 is Day 41130.

Regardless of how the date is formatted for display, Excel only knows it internally by it's Serial Number.

Therefore, you cannot isolate any portion of a Date and change the Font color. All of the cells containing your "Dates" would have to be formatted as Text. However, if the cells are formatted as Text, you may lose some of the functionality that comes with a Date.

Try this:

- Put 8/9/2012 in a cell
- Format it as mm/dd/yyyy
- In the formula bar, highlight the 2012 and pick a font color for the year
- I'm guessing it won't hold when you click out of the formula bar


- Put an apostrophe (single quote) in front of the date to force it to be text '8/9/2012
- In the formula bar, highlight the 2012 and pick a font color for the year
- I'm guessing this time it will work

If you are willing to Format all of the Dates as Text, code similar to this will change the "year" (the last 4 characters) to Red:

Sub RedYear()
  Range("A1").Characters(Start:=Len(Range("A1")) - 3, _
              Length:=4).Font.Color = -16776961
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 10, 2011 at 12:42:40
This totally worked! All I have to do is add an apostrophe for each cell...copy and paste it as a text format and this will work!

Thank you for all your help!

Report •

Related Solutions

Ask Question