Jenedge73
asked on
Change formatting in a document
Good morning.
I export sets of financials out of an accounting system. Everything comes out correctly except one thing. When I copy the exported data into my sheets all the color changes to darkest color of that particular shade, which makes the paper impossible to read. Is there a way to change a particular color in a spreadsheet from one color to another without highlighting (tagging) each cell?
I export sets of financials out of an accounting system. Everything comes out correctly except one thing. When I copy the exported data into my sheets all the color changes to darkest color of that particular shade, which makes the paper impossible to read. Is there a way to change a particular color in a spreadsheet from one color to another without highlighting (tagging) each cell?
The best way I know is to run a small VBA macro to search for and replace the color.
An easy way to start is to record a macro that changes a cell's color to the problem color. With that, the macro will include the problem color and you can change it to a preferred color. Next you'd change the macro to navigate through every active cell in the spreadsheet and if it finds that bad color, change it to the good color.
Would you like to go in this direction?
An easy way to start is to record a macro that changes a cell's color to the problem color. With that, the macro will include the problem color and you can change it to a preferred color. Next you'd change the macro to navigate through every active cell in the spreadsheet and if it finds that bad color, change it to the good color.
Would you like to go in this direction?
ASKER
I think so. I do not know VBA though could you give me a hand?
The first key to understanding VBA/macros is that it's a different application that is set up to communicate with the linked application (in this case Excel.)
To get there from Excel, you can use the View menu tab to access Macros "Record macros". Select that and put a name like FindColor (no spaces) and click the OK button. Back in Excel, add into any cell the color that you want to change. Then go back to the View menu Macros and Stop the Macro recorder.
Now that a macro is recorded, go back to View | Macros | Macros and you should see a window showing the name of the macro you just recorded, FindColor. If not already selected, select it (single-click) and press the [Edit] button to go to the Visual Basic (VB) environment.
From there you should see a window with something like:
Sub FindColor()
'
'
'
...
End Sub
Once you get there, you're ready to add the code to update your file.
Play with that a bit until you feel comfortable with it. If something doesn't work right, close the spreadsheet and start over.
When you're done, copy the FindColor() code here and we'll proceed.
To get there from Excel, you can use the View menu tab to access Macros "Record macros". Select that and put a name like FindColor (no spaces) and click the OK button. Back in Excel, add into any cell the color that you want to change. Then go back to the View menu Macros and Stop the Macro recorder.
Now that a macro is recorded, go back to View | Macros | Macros and you should see a window showing the name of the macro you just recorded, FindColor. If not already selected, select it (single-click) and press the [Edit] button to go to the Visual Basic (VB) environment.
From there you should see a window with something like:
Sub FindColor()
'
'
'
...
End Sub
Once you get there, you're ready to add the code to update your file.
Play with that a bit until you feel comfortable with it. If something doesn't work right, close the spreadsheet and start over.
When you're done, copy the FindColor() code here and we'll proceed.
FYI In the end, your update code will look something like this:
Sub FindColor()
Dim iLastRow As Integer
Dim iLastColumn As Integer
Dim iRow As Integer
Dim iColumn As Integer
Const lSearchColor As Long = 255& * 256
Const lReplaceColor As Long = 255& * 256 * 256
Selection.SpecialCells(xlCellTypeLastCell).Select
iLastRow = Selection.Row
iLastColumn = Selection.Column
For iRow = 1 To iLastRow
For iColumn = 1 To iLastColumn
If Cells(iRow, iColumn).Interior.Color = lSearchColor Then
Cells(iRow, iColumn).Interior.Color = lReplaceColor
End If
Next
Next
End Sub
The two lines starting with Const will contain the numbers for the colors you want to work with, which can be shown in a variety of ways. In the above code, it will replace all Lime-green cells (255& * 256 aka 00FF00) with Bright Blue (255& * 256 * 256 aka FF0000)
ASKER
Ok here it is
Thanks again
Sub FindColor()
'
' FindColor Macro
'
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("D4").Select
End Sub
Thanks again
So it looks like theme colors are the way it worked so I think this should work so add this (or replace the existing one your created) and save the spreadsheet then try to run it.
To run the macro, press Alt+F8 and double-click on FindColor2
Sub FindColor2()
Dim iLastRow As Integer
Dim iLastColumn As Integer
Dim iRow As Integer
Dim iColumn As Integer
Selection.SpecialCells(xlCellTypeLastCell).Select
iLastRow = Selection.Row
iLastColumn = Selection.Column
For iRow = 1 To iLastRow
For iColumn = 1 To iLastColumn
If Cells(iRow, iColumn).Interior. ThemeColor = xlThemeColorDark1 Then
Cells(iRow, iColumn).Interior. ThemeColor = xlThemeColorLight1
End If
Next
Next
End Sub
To run the macro, press Alt+F8 and double-click on FindColor2
ASKER
I have attached a copy of the SS.
I think I may have done something wrong
August-24--collectios-report---D.xlsm
I think I may have done something wrong
August-24--collectios-report---D.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. You guys help me so much best investment ever.
I gotta learn some of this shit
thanks
I gotta learn some of this shit
thanks
Glad it helped!
Select the Page tab, and change the colour theme.
If you want the same colours as in the accounting workbook, hold the mouse over the colour button, wait a moment, and the colour theme will display.
Then select the same theme in the destination workbook.
Or run this to save the theme when the accounting workbook is active.
It is probably the same theme every time, so you only have to save it once.
Open in new window
Then when the other workbook is active
Open in new window