Solved

Change formatting in a document

Posted on 2013-10-23
11
158 Views
Last Modified: 2013-10-27
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?
0
Comment
Question by:Jenedge73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 39595068
Excel uses the colour theme in the destination workbook, not the colours from the workbook you are copying from.
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.

Sub SaveThemeColor()
    ActiveWorkbook.Theme.ThemeColorScheme.Save "c:\themecolor.xml"
End Sub

Open in new window


Then when the other workbook is active

Sub GetThemeColor()
    ActiveWorkbook.Theme.ThemeColorScheme.Load ("c:\themecolor.xml")
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39595070
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?
0
 

Author Comment

by:Jenedge73
ID: 39595164
I think so.  I do not know VBA though could you give me a hand?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:rspahitz
ID: 39595609
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.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39595633
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

Open in new window

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)
0
 

Author Comment

by:Jenedge73
ID: 39598800
Ok here it is
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

Open in new window



Thanks again
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39598834
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.
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

Open in new window


To run the macro, press Alt+F8 and double-click on FindColor2
0
 

Author Comment

by:Jenedge73
ID: 39598870
I have attached a copy of the SS.
I think I may have done something wrong
August-24--collectios-report---D.xlsm
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 39600669
jenedge, I don't think you did anything wrong.  It appears that the way the themecolor works is not quite what I expected.

Try this and see if it helps.  As before, paste this code in, save the workbook, then run it.  If you don't like the results, you can reload the workbook and tweak the numbers to be more what you want.
Sub FindColor2()
    Dim iLastRow As Integer
    Dim iLastColumn As Integer
    Dim iRow As Integer
    Dim iColumn As Integer
    
    Const ColorToCheck As Long = &H993333
    Const ColorToSet As Long = &HCC9999
    Const ColorToCheck2 As Long = &H333333
    Const ColorToSet2 As Long = &H999999
    Const ColorToCheck3 As Long = &H3333
    Const ColorToSet3 As Long = &H669999

    Dim ThemeColor As Double
    
    Selection.SpecialCells(xlCellTypeLastCell).Select
    iLastRow = Selection.Row
    iLastColumn = Selection.Column
    
    For iRow = 1 To iLastRow
        For iColumn = 1 To iLastColumn
            ThemeColor = Cells(iRow, iColumn).Interior.Color
            
            If ThemeColor = ColorToCheck Then
                Cells(iRow, iColumn).Interior.Color = ColorToSet
             
            ElseIf ThemeColor = ColorToCheck2 Then
                Cells(iRow, iColumn).Interior.Color = ColorToSet2

            ElseIf ThemeColor = ColorToCheck3 Then
                Cells(iRow, iColumn).Interior.Color = ColorToSet3
            Else
                Debug.Print Hex(ThemeColor)
           End If
        Next
    Next
End Sub

Open in new window


The numbers to tweak are ColorToSet, ColorToSet2, and ColorToSet3.  Set them as any desired RGB combination as &HBBGGRR where the &H is always there, and the BB is the blue level in Hex, GG is green in hex and RR is red in hex.  For example, for bright blue, use &HFF0000, for dark green use &H008000, and for yellow use &H00FFFF, etc.
0
 

Author Closing Comment

by:Jenedge73
ID: 39600738
Thanks.  You guys help me so much best investment ever.
I gotta learn some of this shit
thanks
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39604826
Glad it helped!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
create different version if file exists 1 45
Excel Date Question 8 46
Excel Auto-Complete lines 4 32
Copy one row (from Word) into one cell 14 49
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question