Cell background colour applied to other cells

**Strazdas** · 08-28-2013, 07:59 AM

Hello.
I have a problem here. I got a sheet that is used as a blank. A user would copy it, rename it what it want, open it, select a year, format a single cell to the colour that he wants, then press a button and it would use this colour to format the tables in other sheets.
Currently for this i am using:

Sub Spalvinti()
    Dim Saltinis
     
    Saltinis = Sheets("Valdymas").Range("D4").Interior.ColorIndex
'ataskaitos
    Sheets("Ataskaitos").Range("B3:F25").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("B27:F38").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("B40:F62").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("B64:F75").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("B77:F99").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("B101:F112").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("J3:N25").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("J27:N38").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("J40:N62").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("J64:N75").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("J77:N99").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("J101:N112").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("A119:B122").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("D119:H122").Interior.ColorIndex = Saltinis
    Sheets("Ataskaitos").Range("A123:H123").Interior.ColorIndex = Saltinis
'SIPS
    Sheets("SIPS").Range("A5:E48").Interior.ColorIndex = Saltinis 'M2010133
    Sheets("SIPS").Range("G5:K48").Interior.ColorIndex = Saltinis 'M2010134
    Sheets("SIPS").Range("A54:E64").Interior.ColorIndex = Saltinis 'M2010274
    Sheets("SIPS").Range("G54:K64").Interior.ColorIndex = Saltinis 'M2010275
    Sheets("SIPS").Range("A70:E107").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("G70:K107").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("A113:E176").Interior.ColorIndex = Saltinis 'M2010278
'SIPS2
    Sheets("SIPS").Range("O5:P136").Interior.ColorIndex = Saltinis 'M2010133
    Sheets("SIPS").Range("R5:R136").Interior.ColorIndex = Saltinis 'M2010133
    
    Sheets("SIPS").Range("U5:V136").Interior.ColorIndex = Saltinis 'M2010134
    Sheets("SIPS").Range("X5:X136").Interior.ColorIndex = Saltinis 'M2010134
    
    Sheets("SIPS").Range("AA5:AB37").Interior.ColorIndex = Saltinis 'M2010274
    Sheets("SIPS").Range("AD5:AD37").Interior.ColorIndex = Saltinis 'M2010274
    
    Sheets("SIPS").Range("AA43:AB75").Interior.ColorIndex = Saltinis 'M2010275
    Sheets("SIPS").Range("AD43:AD75").Interior.ColorIndex = Saltinis 'M2010275
    
    Sheets("SIPS").Range("AG5:AH9").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AJ5:AJ9").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AG11:AH47").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AJ11:AJ47").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AG49:AH85").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AJ49:AJ85").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AG87:AH118").Interior.ColorIndex = Saltinis 'M2010276
    Sheets("SIPS").Range("AJ87:AJ118").Interior.ColorIndex = Saltinis 'M2010276
    
    Sheets("SIPS").Range("AM5:AN9").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AP5:AP9").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AM11:AN47").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AP11:AP47").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AM49:AN85").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AP49:AP85").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AM87:AN118").Interior.ColorIndex = Saltinis 'M2010277
    Sheets("SIPS").Range("AP87:AP118").Interior.ColorIndex = Saltinis 'M2010277
    
    Sheets("SIPS").Range("AS5:AT13").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AV5:AV13").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AS15:AT77").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AV15:AV77").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AS79:AT141").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AV79:AV141").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AS143:AT196").Interior.ColorIndex = Saltinis 'M2010278
    Sheets("SIPS").Range("AV143:AV196").Interior.ColorIndex = Saltinis 'M2010278
End Sub

(i use the comments for my own debugging reason and i know its not a clean and optimized way to deal with ranges, but im not experienced in VBA.)

The problem i face now is that "Interior.ColorIndex" Is.... wrong. It seems to be based on old excel palete with only little choice of colour, and whenever it does not fit that it would pick whatever colour. this result pink turning into yellow and similar travesties. It seems to be chosing wrong colour 2/3 times, which is simply not acceptable.
Is there a way to simply tell the program to copy the background colour identical to the one in the cell? Copying whole formatting destroys the border and text formatting in destination, when i only want the background colour alone.

Excel used: 2007