How are cell formats in one linked workbook carried forward to ano

[Ron Nichols]

I am using formulas to bring cell data from one worksheet to another. I also
want to bring forward the cell formats since some of the cells are color
coded.

Cell formats on the source document may change as the data changes. The
original cell color is manually set when the source spreadsheet is created
but can change as updates occur.

Does anyone have examples of how this is done?

 

[Jim Thomlinson]

There is no easy way to carry the formats forward. XL functions return values
only. There are no functions that return formats. To do what youwant will
require custom macros. If you want to persue that reply back. Note that the
proposed macros are not simple in nature.

 

[Ron Nichols]

Thanks for the information. I have limited experience with macros but would
be interested in an examble that checks the background color of one cell and
sets the current cell based on the results.

Thanks

Ron Nichols

 

[Jim Thomlinson]

You can not read the colours of a closed workbook. So you need code that
opens the book and then you can use a procedure similar to this to read the
interior colour of a cell and use that to set the values of your specified
cells. Once you have coloured the cells then you can close the workbook
again. Probably best to attach all of this to a command button as that would
be the easiest to impliment...

Untested but is will look something like this...

public sub ColourSheet()
dim wbk as workbook

set wbk = workbooks.open(Filename:="c:\MyBook.xls", ReadOnly:=True)

thisworkbook.sheets("Sheet1").Range("A1").interior.colorindex = _
wbk.sheets("Sheet1").range("A1").interior.colorindex

thisworkbook.sheets("Sheet1").Range("A2").interior.colorindex = _
wbk.sheets("Sheet1").range("A2").interior.colorindex

wbk.close SaveChanges:=False
end sub