Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format to Value
I have an excel spreadsheet that has many columns filled black. It
looks great, but I need to replace the formatting with an actual value, These squares are black with no value in them. Is there some way to add a value to all squares that are black? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format to Value
use white text.
black text in black cells will be invisible. hth susan On Apr 9, 9:01 am, wrote: I have an excel spreadsheet that has many columns filled black. It looks great, but I need to replace the formatting with an actual value, These squares are black with no value in them. Is there some way to add a value to all squares that are black? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format to Value
Try this procedure.
Sub FilledBlack() Dim R As Range, ER As Range Set ER = ThisWorkbook.Worksheets("Sheet1").Range("A1:F10000 ") For Each R In ER.Cells If R.Interior.ColorIndex = 1 Then R.Value = "Your Value" End If Next R End Sub Substitute whatever the range you want to check for "A1:F10000" ColorIndex = 1 checks to see if the cell is filled black. This will enter the value "Your Value" in all cells that are filled black. Hope this gets you started. Bill " wrote: I have an excel spreadsheet that has many columns filled black. It looks great, but I need to replace the formatting with an actual value, These squares are black with no value in them. Is there some way to add a value to all squares that are black? Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format to Value
On Apr 9, 8:42 am, William Horton
wrote: Try this procedure. Sub FilledBlack() Dim R As Range, ER As Range Set ER = ThisWorkbook.Worksheets("Sheet1").Range("A1:F10000 ") For Each R In ER.Cells If R.Interior.ColorIndex = 1 Then R.Value = "Your Value" End If Next R End Sub Substitute whatever the range you want to check for "A1:F10000" ColorIndex = 1 checks to see if the cell is filled black. This will enter the value "Your Value" in all cells that are filled black. Hope this gets you started. Bill " wrote: I have an excel spreadsheet that has many columns filled black. It looks great, but I need to replace the formatting with an actual value, These squares are black with no value in them. Is there some way to add a value to all squares that are black? Thanks,- Hide quoted text - - Show quoted text - Thanks so much! You just saved me a whole day's worth of manual editing! I knew there had to be a way to reference the formatting.-- Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel | |||
Keep format after paste from other worksheets - conditional format or EnableControl solution doesn't work | Excel Programming |