Solved Excel Macro Change Font Color

Microsoft Excel 2003 (full product)
April 23, 2010 at 18:06:45
Specs: Windows XP
Can someone help me create a macro to accomplish the following. I would like to create a macro that changes the font color of text in a cell in a column depending on the word that is input. For example, when the word Green is in the cell the font color would be green, when the word Red is in the cell the font color would be red, when the word Blue is in the cell the font color would be blue. I need to define 10 word variables. I am limited to 3 color changes with conditional formatting in excel 2003.

Many Thanks


See More: Excel Macro Change Font Color

Report •

#1
April 23, 2010 at 21:38:29
✔ Best Answer
Select the sheet tab for the sheet where you want this to happen.

Choose View Code and paste this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
'Set Font Color based on Target cell's contents
   Select Case Target
    Case "Red"
     Target.Font.ColorIndex = 3
    Case "Blue"
     Target.Font.ColorIndex = 5
    Case "Green"
     Target.Font.ColorIndex = 10
'Repeat for 7 more Cases
'
'Set font color to Automatic for all other entries
    Case Else
     Target.Font.ColorIndex = 0
   End Select
End Sub

If you need to determine the ColorIndex number for all 56 colors, run the following code in a blank worksheet. The Row number next to each color is the ColorIndex number to use to get that Font or Fill color.

Sub WhatColorNum()
  For colorNum = 1 To 56
   Cells(colorNum, 1).Interior.ColorIndex = colorNum
  Next
End Sub

Hope that helps!


Report •

#2
April 24, 2010 at 02:22:42
This works great when entering text. However, I am using VLOOKUP to update the cell contents. The code does not refresh the font color when the value changes. I am not skilled at editing the code into a macro that will update the text content in a range of cells. The cell content will be in column G. Can you please modify the code to update the cell content in column G given the criteria in the code provided. Your help is most appreciated!

Report •

#3
April 24, 2010 at 04:05:50
A posting tip:

It helps us help you if you provide as much detail as possible when requesting a solution.

I don't think too many of us could have interpreted:

"a macro that changes the font color of text in a cell in a column depending on the word that is input"

to actually mean:

"a macro that changes the font color of text in a cell that contains a VLOOKUP function dependent on a word that is input elsewhere in the spreadsheet."

A quick answer would be to simply suggest a Macro that scans Column G after every change to the worksheet looking for your 10 words and setting the Font color based on what it finds. However, that would probably end up being extremely inefficient.

Why not give us a little detail as to where the input occurs, what the VLOOKUP is doing, the range of cells that need the Font color changed, etc.

The more you can tell us about how the 10 words end up in the cells, the better the solutions we can offer.


Report •

Related Solutions

#4
April 24, 2010 at 05:17:53
Thanks for the direction. I have a data worksheet that holds 100's of rows of data. Each row represents a project. One of the many columns within the worksheet is the projects status. Each projects status is represented by one of 10 color text inputs in one of the cells in the row. The data is copy and pasted into the worksheet from a project management tool.

My task is to summarize specific projects in a summary worksheet tab within the spreadsheet listing the project and status. I use VLOOKUP to locate the data in the data worksheet and present in the summary worksheet tab. The projects status change monthly, so I will refresh the data in the data worksheet tab, thus updating the data in the summary worksheet tab automatically via VLOOKUP.

My goal is to change the font color of the status in the summary worksheet tab each time I update the data. I already have a macro to update the cell color to match the status color. I would also like to create a macro to change the font color to match the status color. The project status is located in column G of the summary worksheet tab.

Hope this is clear. Thanks


Report •

#5
April 24, 2010 at 17:14:47
re: I already have a macro to update the cell color to match the status color. I would also like to create a macro to change the font color to match the status color.

Can we assume that you are not trying to change the font color in the same cell as the status color? If you do, the text will disappear.

So assuming you are changing the font color in a different cell, my first suggestion would be to include the font color change in the same macro as the cell color change.

For example, whatever criteria is used to set the cell color of C6 to green, should also be able to set the font color in G6 to green.

In order to see if that will work, we'd need to see the code that sets the cell color.

Can you post that code?


Report •

#6
April 25, 2010 at 05:25:15
Many thanks for your help. Yes I am looking for the text to disappear by changing the cell & font colors to be the same in the status cell. Here is the macro I am using to change the cell color. If this can be modified to also change the font color that would be awesome.


Sub Cell_Color()

Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
'Start at row 1
LRow = 1

'Update row colors for the first 200 rows
While LRow < 201
LCell = "G" & LRow
'Color will changed in columns G to G
LColorCells = "G" & LRow & ":" & "G" & LRow

Select Case Left(Range(LCell).Value, 6)

'Set row color to green
Case "Green"
Range(LColorCells).Interior.ColorIndex = 10
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to black
Case "Black"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 1
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to blue
Case "Blue"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 32
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to gray
Case "Gray"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 15
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to orange
Case "Orange"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 45
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to pink
Case "Pink"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 38
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to purple
Case "Purple"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 13
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to tan
Case "Tan"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 40
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to yellow
Case "Yellow"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 6
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to red
Case "Red"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 3
Range(LColorCells).Interior.Pattern = xlSolid

'Default all other rows to no color
Case Else
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = xlNone

End Select

LRow = LRow + 1
Wend

Range("A1").Select

End Sub


Report •

#7
April 25, 2010 at 18:06:29
It seems like this should be pretty simple:

Everywhere you have something like:

Range(LColorCells).Interior.ColorIndex = 10
Range(LColorCells).Interior.Pattern = xlSolid

just add this line afterward:

Range(LColorCells).Font.ColorIndex = 10

.Interior.ColorIndex sets the cell fill color

.Interior.Pattern tells Excel to fill the cell with a solid color (which usually isn't necessary since that's the default)

.Font.ColorIndex sets the font color.

Match the .Interior.ColorIndex and the .Font.ColorIndex and the text will disappear.



Report •

#8
April 26, 2010 at 04:32:28
Thank you very much!

Report •

Ask Question