Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing font colors...
Normally changing the font color is a simple task, right? Well, I've got a
poser for y'all. (Excel 2003, Win 2000, fully patched) My boss keeps a spreadsheet of issues/resolutions on the tanks (vehicles, not vessels) at work. Each row is a separate vehicle. One of the columns has multiple lines of text with manual line breaks between each issue. If an issue hasn't been discussed at our 3x weekly meeting, that specific line of text is changed to green. After it's discussed, it's changed to black. Got this so far? Okay..........we use a projector in the 3x weekly meeting to display this spreadsheet. The colors are muddied so I've changed that green text to bright green so it is obviously not black on the projection screen. I have been trying to use Find/Replace to find any green text and change it to bright green. Unfortunately, it will not hit on any cells that doesn't contain text in mixed colors. It's an all or nothing. I've tried selecting the "Match entire cell contents" and deselecting it. No difference. It's difficult to see the green text and I would love to be able to do something simple like this to change it all in one shot. Can it be done? Can it be done by someone who knows diddly about VBA? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing font colors...
JoAnn,
It sound like you have already changed the colors in the cells where every character is already green. That would leave the cells with two or more font colors in them. Vba code could go thru the used range on the sheet and determine each cell with mixed font colors. Then for each of those cells it would have to read the font color for each character and if it was dark green in color then change it to bright green. Or it could just look for any character that wasn't colored "automatic" and change it to bright green. If you have 10,000 tanks and a small book written about each one then the procedure would probably take a while. Does that describe the situation and/or help clarify it? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "JoAnn Paules [MVP]" wrote in message Normally changing the font color is a simple task, right? Well, I've got a poser for y'all. (Excel 2003, Win 2000, fully patched) My boss keeps a spreadsheet of issues/resolutions on the tanks (vehicles, not vessels) at work. Each row is a separate vehicle. One of the columns has multiple lines of text with manual line breaks between each issue. If an issue hasn't been discussed at our 3x weekly meeting, that specific line of text is changed to green. After it's discussed, it's changed to black. Got this so far? Okay..........we use a projector in the 3x weekly meeting to display this spreadsheet. The colors are muddied so I've changed that green text to bright green so it is obviously not black on the projection screen. I have been trying to use Find/Replace to find any green text and change it to bright green. Unfortunately, it will not hit on any cells that doesn't contain text in mixed colors. It's an all or nothing. I've tried selecting the "Match entire cell contents" and deselecting it. No difference. It's difficult to see the green text and I would love to be able to do something simple like this to change it all in one shot. Can it be done? Can it be done by someone who knows diddly about VBA? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing font colors...
This code should so it, but as Jim says, it could take a long time
Public Sub ProcessData() Const TestColumn As String = "D" '<=== change to suit Const OriginalCI As Long = 10 '<=== green, change to suit Const NewCI As Long = 4 '<=== lime green, change to suit Dim iLastRow As Long Dim i As Long, j As Long With ActiveSheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual iLastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row For i = 1 To iLastRow For j = 1 To Len(Cells(i, TestColumn).Value) Debug.Print If Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = OriginalCI Then Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = NewCI End If Next j Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JoAnn Paules [MVP]" wrote in message ... Normally changing the font color is a simple task, right? Well, I've got a poser for y'all. (Excel 2003, Win 2000, fully patched) My boss keeps a spreadsheet of issues/resolutions on the tanks (vehicles, not vessels) at work. Each row is a separate vehicle. One of the columns has multiple lines of text with manual line breaks between each issue. If an issue hasn't been discussed at our 3x weekly meeting, that specific line of text is changed to green. After it's discussed, it's changed to black. Got this so far? Okay..........we use a projector in the 3x weekly meeting to display this spreadsheet. The colors are muddied so I've changed that green text to bright green so it is obviously not black on the projection screen. I have been trying to use Find/Replace to find any green text and change it to bright green. Unfortunately, it will not hit on any cells that doesn't contain text in mixed colors. It's an all or nothing. I've tried selecting the "Match entire cell contents" and deselecting it. No difference. It's difficult to see the green text and I would love to be able to do something simple like this to change it all in one shot. Can it be done? Can it be done by someone who knows diddly about VBA? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing font colors...
You guys understand the problem perfectly. It's not a huge spreadsheet.
Maybe 200 rows and the font in question is all in one column. I'll try the code but I've never had much luck at running codes. I'm a complete dunce with them. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Bob Phillips" wrote in message ... This code should so it, but as Jim says, it could take a long time Public Sub ProcessData() Const TestColumn As String = "D" '<=== change to suit Const OriginalCI As Long = 10 '<=== green, change to suit Const NewCI As Long = 4 '<=== lime green, change to suit Dim iLastRow As Long Dim i As Long, j As Long With ActiveSheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual iLastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row For i = 1 To iLastRow For j = 1 To Len(Cells(i, TestColumn).Value) Debug.Print If Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = OriginalCI Then Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = NewCI End If Next j Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JoAnn Paules [MVP]" wrote in message ... Normally changing the font color is a simple task, right? Well, I've got a poser for y'all. (Excel 2003, Win 2000, fully patched) My boss keeps a spreadsheet of issues/resolutions on the tanks (vehicles, not vessels) at work. Each row is a separate vehicle. One of the columns has multiple lines of text with manual line breaks between each issue. If an issue hasn't been discussed at our 3x weekly meeting, that specific line of text is changed to green. After it's discussed, it's changed to black. Got this so far? Okay..........we use a projector in the 3x weekly meeting to display this spreadsheet. The colors are muddied so I've changed that green text to bright green so it is obviously not black on the projection screen. I have been trying to use Find/Replace to find any green text and change it to bright green. Unfortunately, it will not hit on any cells that doesn't contain text in mixed colors. It's an all or nothing. I've tried selecting the "Match entire cell contents" and deselecting it. No difference. It's difficult to see the green text and I would love to be able to do something simple like this to change it all in one shot. Can it be done? Can it be done by someone who knows diddly about VBA? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing font colors...
Just select the sheet in question, then go into the VBIDE (Alt-F11).
In the VBIDE, insert a code module (InsertModule) Paste the code into the code pane. Make changes to the customisable bits that I indicated. Put the cursor somewhere in the code, anywhere, then hit F5 to run it. Go back to Excel to check the results. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JoAnn Paules [MVP]" wrote in message ... You guys understand the problem perfectly. It's not a huge spreadsheet. Maybe 200 rows and the font in question is all in one column. I'll try the code but I've never had much luck at running codes. I'm a complete dunce with them. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Bob Phillips" wrote in message ... This code should so it, but as Jim says, it could take a long time Public Sub ProcessData() Const TestColumn As String = "D" '<=== change to suit Const OriginalCI As Long = 10 '<=== green, change to suit Const NewCI As Long = 4 '<=== lime green, change to suit Dim iLastRow As Long Dim i As Long, j As Long With ActiveSheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual iLastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row For i = 1 To iLastRow For j = 1 To Len(Cells(i, TestColumn).Value) Debug.Print If Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = OriginalCI Then Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = NewCI End If Next j Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JoAnn Paules [MVP]" wrote in message ... Normally changing the font color is a simple task, right? Well, I've got a poser for y'all. (Excel 2003, Win 2000, fully patched) My boss keeps a spreadsheet of issues/resolutions on the tanks (vehicles, not vessels) at work. Each row is a separate vehicle. One of the columns has multiple lines of text with manual line breaks between each issue. If an issue hasn't been discussed at our 3x weekly meeting, that specific line of text is changed to green. After it's discussed, it's changed to black. Got this so far? Okay..........we use a projector in the 3x weekly meeting to display this spreadsheet. The colors are muddied so I've changed that green text to bright green so it is obviously not black on the projection screen. I have been trying to use Find/Replace to find any green text and change it to bright green. Unfortunately, it will not hit on any cells that doesn't contain text in mixed colors. It's an all or nothing. I've tried selecting the "Match entire cell contents" and deselecting it. No difference. It's difficult to see the green text and I would love to be able to do something simple like this to change it all in one shot. Can it be done? Can it be done by someone who knows diddly about VBA? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing font colors...
TA DUM!!! Give the man a see-gar!
Yes, the font changed colors. Plus I edited that code to change it back again. The lime green shows up great on a projected image but it difficult to see on a CRT monitor. This way I can change the text prior to our meeting and then back again afterward. Thank you ever so much. I actually stopped at Borders on the way home from work and picked up "Excel VBA Programming for Dummies". "Excel Programming for Drooling Imbeciles" would have been better for me but it doesn't exist. Let's hope I'm a dummy. ;-) -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Bob Phillips" wrote in message ... Just select the sheet in question, then go into the VBIDE (Alt-F11). In the VBIDE, insert a code module (InsertModule) Paste the code into the code pane. Make changes to the customisable bits that I indicated. Put the cursor somewhere in the code, anywhere, then hit F5 to run it. Go back to Excel to check the results. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JoAnn Paules [MVP]" wrote in message ... You guys understand the problem perfectly. It's not a huge spreadsheet. Maybe 200 rows and the font in question is all in one column. I'll try the code but I've never had much luck at running codes. I'm a complete dunce with them. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Bob Phillips" wrote in message ... This code should so it, but as Jim says, it could take a long time Public Sub ProcessData() Const TestColumn As String = "D" '<=== change to suit Const OriginalCI As Long = 10 '<=== green, change to suit Const NewCI As Long = 4 '<=== lime green, change to suit Dim iLastRow As Long Dim i As Long, j As Long With ActiveSheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual iLastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row For i = 1 To iLastRow For j = 1 To Len(Cells(i, TestColumn).Value) Debug.Print If Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = OriginalCI Then Cells(i, TestColumn).Characters(j, 1).Font.ColorIndex = NewCI End If Next j Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JoAnn Paules [MVP]" wrote in message ... Normally changing the font color is a simple task, right? Well, I've got a poser for y'all. (Excel 2003, Win 2000, fully patched) My boss keeps a spreadsheet of issues/resolutions on the tanks (vehicles, not vessels) at work. Each row is a separate vehicle. One of the columns has multiple lines of text with manual line breaks between each issue. If an issue hasn't been discussed at our 3x weekly meeting, that specific line of text is changed to green. After it's discussed, it's changed to black. Got this so far? Okay..........we use a projector in the 3x weekly meeting to display this spreadsheet. The colors are muddied so I've changed that green text to bright green so it is obviously not black on the projection screen. I have been trying to use Find/Replace to find any green text and change it to bright green. Unfortunately, it will not hit on any cells that doesn't contain text in mixed colors. It's an all or nothing. I've tried selecting the "Match entire cell contents" and deselecting it. No difference. It's difficult to see the green text and I would love to be able to do something simple like this to change it all in one shot. Can it be done? Can it be done by someone who knows diddly about VBA? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Font Colors | Setting up and Configuration of Excel | |||
Changing *permanently* Excel's colors and graph fill for charting? | Charts and Charting in Excel | |||
changing colors of font in functions | Excel Worksheet Functions | |||
Changing default font for copy / paste | Excel Discussion (Misc queries) | |||
Changing font size in a list box | Excel Discussion (Misc queries) |