Excel formula to retain font color?

December 22, 2009 at 11:54:15
Specs: Windows Vista
Hello. I am a pretty strong user of excel but need some help with a formula.

I will simplify the details for sake of argument. I have two sheets, sheet 1 and sheet 2.

I am pulling in a number from sheet 2, to sheet 1. On sheet 2, this number is the color blue.

When it pulls into sheet 1, the color defaults to black. Is there a way to pull in the information from sheet 2 and maintain the formatting in place on that sheet?

I don't want to manually update the color because this is a process that needs to be done weekly and the numbers on sheet are changing colors weekly. blue=price not final, black=price final.

Any help would be appreciated, thanks.

See More: Excel formula to retain font color?

Report •

December 22, 2009 at 12:07:50
Unless they've added something to Excel 2007 (which I don't have) there are no built-in functions in Excel to change the color of a font.

You might be able to use Conditional Formatting, you might be able to use a User Defined Function written in VBA or you might be able to use a Worksheet_Change macro.

Perhaps if you didn't "simplify the details for sake of argument" we could offer a more specific solution.

Please supply some details and we'll see how we can help.

P.S. I changed the title of your post to be a little less generic. The threads in this forum would be impossible to follow if we used titles like "Excel Help" or "Formula Help".

Report •

December 22, 2009 at 12:54:12
Thanks. I am new to the whole message board world.

I am work in the chemical sales world. Every friday I recieve an updated download of raw material prices. Maybe 5 columns across the top of various materials, and 15 rows down the left side with dates. January 2009, February 2009, March 2009, etc. Each of these cells have cost information. If the font is black, it means the price is settled, if the price is blue, it means the price can still change.

The sheet above is provided by an outside source and I have no way to change the way I recieve the information.

I use the spreadsheet above to update my own internal pricing schedules. When I pull in the price from the external schedule to my own internal schedule, the font color defaults to black. I then need to manually go back and review the prices to see which ones are blue and black and update accordingly in my spreadsheet.

If I could carry the font color with my formula, I wouldn't need to go back and do all these manual updates.

Report •

December 22, 2009 at 12:59:37
re: When I pull in the price from the external schedule to my own internal schedule...

How are you "pulling" the data?

If you are using a formulae, please post an example.

If you are using a macro, please post the VBA code.

Once again, the more detail you provide, the more specific a solution we can offer.

Report •

Related Solutions

December 22, 2009 at 13:08:07
The formula is as simple as =Sheet2!B1

The outside file and my internal files stay in the exact same format every month, the only that changes is the external download. I get the new download and copy and paste it over the prior week download. When I paste it over last week's prices, my spreadsheet updates. Because all the prices update to black, I need to go back and check the new download to see what prices switched from blue (still negotiating price) to black (final price).

Report •

December 22, 2009 at 14:57:17
Never mind this post..although this method should work, see the next response for a slightly better way to accomplish your goal.

OK, I have to make few assumptions here. Correct me if I am wrong because there are probably other ways to do this depending on the layout of your data. This is just a first shot and I would need to know more specifics to clean it up a bit. If you like the concept, we can work on it some more. It's far from perfect.

First, I'll assume that the layout of your own sheet does not match the layout of the external data. In other words, the formulae in Sheet1 isn't nice and "neat" like this...

A1 contains =SHEET2!A1
B1 contains =SHEET2!B1

but more like:

A1 contains =SHEET2!O45
B1 contains =SHEET2!C11

i.e. no real one-to-one pattern.

I'll also assume that you have enough room in Sheet1 to fit all of the external data as well as your own data. I'll explain why later.

And finally, I'll assume that the simple formula you posted - where only one cell is referenced - is really what you are using.

Here's what I did...

I set up Sheet1 with a bunch of formulae referenceing cells in Sheet2, like I assume you have now.

I selected the cells with the formulae in Sheet1 and made it a Named Range - I named it myRng.

I then selected all of the data on Sheet2 and cut/pasted it into Sheet1, out of the way where it won't bother me. In my test, I started the paste in M1. All of the colors should have been retained.

This process (cut/paste) should have updated all of the formulae so that they now reference the data in Sheet1, not Sheet2.

Next, right click the sheet tab for Sheet1 and choose View Code.

Paste this code into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
'Error handler for cells without precedents
On Error Resume Next
'Check location of change
  If Not Intersect(Target, Range("$M$1")) Is Nothing Then
'If new data was pasted in, loop through precedents and set Font Color
  For Each myCell In Range("myRng")
    myCell.Font.ColorIndex = myCell.Precedents.Font.ColorIndex
  End If
End Sub

From now on, paste your external data into the same place in Sheet1, not Sheet2.

What this does is monitor Sheet1 for changes. In my example, if the change it sees started in M1, it means you pasted in new data. It will ignore any changes to the sheet that do not include M1.

The code will then loop thorugh the cells in myRng, "read" the formula in each, determine the "Precendent" for that cell (the cell that the formula refers to) and then set the Font Color to the same Font Color as the Precendent.

The reason the data has to be in Sheet1 is that the VBA Precedent property only works on cells in the same sheet, so it can't "locate" the Precedents if they are on a different sheet.

If the layout of your sheets is in some kind of sequential order, then it may be possible to loop through the cells based on their addresses (instead of their precedents) and not have to move the data into Sheet1. You'd have to supply those details before we can offer something else.

Let me know if that is of any help.

Report •

December 22, 2009 at 16:35:23
I was think too deeply in my previous response. This method is much simpler...

Select the cells on Sheet1 that contain the formulae that refer to the data in Sheet2.

Name the selected cells myRng.

Right Click the sheet tab for Sheet2 and paste this code in:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'Loop through myRng
  For Each c In Sheets(1).Range("myRng")
'Strip off "=SHEET1!" from the formula, leaving just
'the cell reference e.g. E11
   addStr = Right(c.Formula, Len(c.Formula) _
            - WorksheetFunction.Find("!", c.Formula))
'Set the Font color based on the Font color of the
'referenced cell in Sheet2
   c.Font.ColorIndex = Sheets(2).Range(addStr).Font.ColorIndex
End Sub

With this code there is no need to copy the data from Sheet2 into Sheet1. When you paste the new data over the old in Sheet2, the code will fire and update the font color of the data in Sheet1.

Report •

Ask Question