Wrong color display in Excel

Microsoft Excel 2003 (full)
August 17, 2010 at 01:51:32
Specs: Windows XP
An Excel sheet is created in MS Excel 2007 having some cells colored in Green. When the file is viewed in Excel 2003, the color of the cells are shown in yellow.
How to correct this problem ?

With regards,
N C Sudhakar


See More: Wrong color display in Excel

Report •


#1
August 17, 2010 at 04:03:06
EDIT
   Please follow Humar's advice in Solution 2 first.


Sounds like you have two different colour profiles.

I have no idea how that happens.

Try this macro:

Sub ResetColours()

    Workbooks("SomeBook.xls").ResetColors

End Sub

I haven't tested it, so I don't know if it will help.

If it doesn't work...


My Excel 2003 will randomly switch to another colour profile.
It seems to happen when I open any file based on an old template.

I use the following macro to reset my colour profile.

Please Note:
This is for Excel 2003 only.

Sub SetColourProfile()

' SetColourProfile Macro
' This Macro will reset the Colour Profile on the Active Workbook.

' ***************************************************************

' I ran into an odd problem.
' The Colour Profile of the Active Workbook will change.

' For instance:
'   Black will be White.
'   Pink will be Grey.
'   Red is completely gone.

' I have no idea what caused it.
' Or why/how it happens.
' The change happen while I am busy with the Workbook.
' It always change to the same Profile.

' ***************************************************************

Application.ScreenUpdating = False
' Hides the "Screen-Flickering" while the Macro is busy.
    
    ActiveWorkbook.Colors(1) = RGB(0, 0, 0)
    ActiveWorkbook.Colors(2) = RGB(255, 255, 255)
    ActiveWorkbook.Colors(3) = RGB(255, 0, 0)
    ActiveWorkbook.Colors(4) = RGB(0, 255, 0)
    ActiveWorkbook.Colors(5) = RGB(0, 0, 255)
    ActiveWorkbook.Colors(6) = RGB(255, 255, 0)
    ActiveWorkbook.Colors(7) = RGB(255, 0, 255)
    ActiveWorkbook.Colors(8) = RGB(0, 255, 255)
    ActiveWorkbook.Colors(9) = RGB(128, 0, 0)
    ActiveWorkbook.Colors(10) = RGB(0, 128, 0)
    ActiveWorkbook.Colors(11) = RGB(0, 0, 128)
    ActiveWorkbook.Colors(12) = RGB(128, 128, 0)
    ActiveWorkbook.Colors(13) = RGB(128, 0, 128)
    ActiveWorkbook.Colors(14) = RGB(0, 128, 128)
    ActiveWorkbook.Colors(15) = RGB(192, 192, 192)
    ActiveWorkbook.Colors(16) = RGB(128, 128, 128)
    ActiveWorkbook.Colors(17) = RGB(153, 153, 255)
    ActiveWorkbook.Colors(18) = RGB(153, 51, 102)
    ActiveWorkbook.Colors(19) = RGB(255, 255, 204)
    ActiveWorkbook.Colors(20) = RGB(204, 255, 255)
    ActiveWorkbook.Colors(21) = RGB(102, 0, 102)
    ActiveWorkbook.Colors(22) = RGB(255, 128, 128)
    ActiveWorkbook.Colors(23) = RGB(0, 102, 204)
    ActiveWorkbook.Colors(24) = RGB(204, 204, 255)
    ActiveWorkbook.Colors(25) = RGB(0, 0, 128)
    ActiveWorkbook.Colors(26) = RGB(255, 0, 255)
    ActiveWorkbook.Colors(27) = RGB(255, 255, 0)
    ActiveWorkbook.Colors(28) = RGB(0, 255, 255)
    ActiveWorkbook.Colors(29) = RGB(128, 0, 128)
    ActiveWorkbook.Colors(30) = RGB(128, 0, 0)
    ActiveWorkbook.Colors(31) = RGB(0, 128, 128)
    ActiveWorkbook.Colors(32) = RGB(0, 0, 255)
    ActiveWorkbook.Colors(33) = RGB(0, 204, 255)
    ActiveWorkbook.Colors(34) = RGB(204, 255, 255)
    ActiveWorkbook.Colors(35) = RGB(204, 255, 204)
    ActiveWorkbook.Colors(36) = RGB(255, 255, 153)
    ActiveWorkbook.Colors(37) = RGB(153, 204, 255)
    ActiveWorkbook.Colors(38) = RGB(255, 153, 204)
    ActiveWorkbook.Colors(39) = RGB(204, 153, 255)
    ActiveWorkbook.Colors(40) = RGB(255, 204, 153)
    ActiveWorkbook.Colors(41) = RGB(51, 102, 255)
    ActiveWorkbook.Colors(42) = RGB(51, 204, 204)
    ActiveWorkbook.Colors(43) = RGB(153, 204, 0)
    ActiveWorkbook.Colors(44) = RGB(255, 204, 0)
    ActiveWorkbook.Colors(45) = RGB(255, 153, 0)
    ActiveWorkbook.Colors(46) = RGB(255, 102, 0)
    ActiveWorkbook.Colors(47) = RGB(102, 102, 153)
    ActiveWorkbook.Colors(48) = RGB(150, 150, 150)
    ActiveWorkbook.Colors(49) = RGB(0, 51, 102)
    ActiveWorkbook.Colors(50) = RGB(51, 153, 102)
    ActiveWorkbook.Colors(51) = RGB(0, 51, 0)
    ActiveWorkbook.Colors(52) = RGB(51, 51, 0)
    ActiveWorkbook.Colors(53) = RGB(153, 51, 0)
    ActiveWorkbook.Colors(54) = RGB(153, 51, 102)
    ActiveWorkbook.Colors(55) = RGB(51, 51, 153)
    ActiveWorkbook.Colors(56) = RGB(51, 51, 51)

Application.ScreenUpdating = True

' Let the user know that the Colour Profile has been set.
   MsgBox ("ColourProfile Set!")

End Sub

Hope it helps for you.


Report •

#2
August 17, 2010 at 05:21:26
Hi,

This is likely a version issue..

Excel 2007 has a much larger colour palette available than earlier versions of Excel. When the file is opened in an earlier version, a color conversion occurs.

In Excel 2007 try saving the file as an Excel 2003 compatible file - you will get warnings about features that cannot be properly converted.

You might be able to deal with the issue by correcting the colour with the workbook open in Excel 2003, saving it, and opening the modified workbook in Excel 2007, and re-saving it in Excel 2007.

I don't have Excel 2007 on this PC, so I can't test if this actually works !!!

Regards


Report •

#3
August 17, 2010 at 20:52:50
Dear Humar,

Thank you for the solution. Really strange that black becomes white and green becomes yellow ! Because in some of our statistics, based on the colors, we treat them as various alerts.
Thanks again for the quick reply.
Regards,
N C Sudhakar


Report •

Related Solutions

#4
August 18, 2010 at 03:50:04
You're welcome.

Regards

Humar


Report •

#5
October 22, 2010 at 17:06:28
Hello Lantere,
I too have the problem of an active worksheet that suddenly lost its color red and made it white. I have never written a macro, tried to do it in Excel and having no luck. I saw how to "record" but I don't know how to put in the information you described in your post. Can you help this novice??

Report •

#6
October 25, 2010 at 05:04:50
Hi, Grassoe.
Sorry I reply now, I wasn't online for a few days.

Remember, my solution is for Excel 2003.
I seriously doubt it will work on a later version.

I will assume you do not currently have any macros in your Personal Macro Workbook.
If you do, ignore Steps a to c and begin at Step 0.

Step a:
Record a new macro.
(Excel doesn't create the Personal Macro Workbook unless you record a macro into it.)

Step b: (In the Macro Recorder Dialogue box:)
Give the macro a name if you want.
Don't bother with a Shortcut Key, unless you want.
Store the Macro in Personal Macro Workbook (in the drop-down list.)
If you want, you can give it a description.
Click on OK.

Step c: (The Macro Recorder:)
Click anywhere in the sheet. Doesn't matter what cell you select.
(This is just to make sure the Personal Macro Workbook gets created.)
Click on the "Stop Recording" button. (It looks like a square.)

Step 0:
(You can do this before Step a if you want.)
Open your "Missing Colours" Workbook.

Step 1:
Hold Alt and press F11.
The Microsoft Visual Basic Editor will open.

Step 2:
Locate your Personal Macro Workbook in the VBA Project list.
(The VBA Project list should be at the top left of the window.)
Your Personal Macro Workbook will be called "VBAProject(PERSONAL.XLS)".
Click on the "+" next to it.
Click on the "+" next to "Modules".
Look for "Module1".
(If you already have macros, then look for the latest Module Number.)

Step 2:
Double click "Module1".
The Frame to the right will open up the macros contained in this module.
Locate your macro.
If you gave it a name in Step b, it will be "Sub NameYouGaveEarlier()".
Otherwise, it will be "Sub Macro1()" or some other number.

Step 3:
Copy my code from Solution #1.
Use either code.
I suggest trying this one first:

Sub ResetColours()

    Workbooks("SomeBook.xls").ResetColors

End Sub

Step 4:
Select your macro from "Sub NameYouGaveEarlier" up to (and including) "End Sub".
Paste my macro.

Step 5: IMPORTANT
Save your Personal Macro Workbook.
(Just hold Ctrl and press "S".)

Step 6:
Click anywhere inside the macro.
(Between "Sub" and "End Sub".
Click the Green Arrow on the toolbar.
(Pressing "F5" will also work.)

When done, close the VBA Editor.
Hopefully your Sheet should have the correct colours.
If not, you can try the second macro.
Although the results should be the same.


Report •


Ask Question