| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replaceformat problem
I have a macro which worked perfectly this morning which replaced values with
the same value and replaced the interior colour depending on the value within the cell. I added a select statement with the with the selection of "" to be replaced by "" and the search to be xlNone. This did not produce the desired result and so I removed this piece of code. The problem now is that no colour changes occur in the spreadsheet although the code is the same. Has anyone came across this problem? I have added the relevant code below. Thanks in advance Davie Sub EDITcolumnSCORES() Columns("h:m").Select Selection.ColumnWidth = 8 Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With Application.ReplaceFormat.Interior.ColorIndex = 1 Application.ReplaceFormat.Font.ColorIndex = 2 Application.ReplaceFormat.Orientation = 90 Selection.Replace What:="Crystalised", Replacement:="Crystalised", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 10 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="H", Replacement:="H", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 45 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="MH", Replacement:="MH", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 6 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="ML", Replacement:="ML", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 4 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="L", Replacement:="L", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Replaceformat problem
This problem seems to have been machine specific. The macro works fine
today. Don't you think computing is wonderfull!!! "davethewelder" wrote: I have a macro which worked perfectly this morning which replaced values with the same value and replaced the interior colour depending on the value within the cell. I added a select statement with the with the selection of "" to be replaced by "" and the search to be xlNone. This did not produce the desired result and so I removed this piece of code. The problem now is that no colour changes occur in the spreadsheet although the code is the same. Has anyone came across this problem? I have added the relevant code below. Thanks in advance Davie Sub EDITcolumnSCORES() Columns("h:m").Select Selection.ColumnWidth = 8 Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With Application.ReplaceFormat.Interior.ColorIndex = 1 Application.ReplaceFormat.Font.ColorIndex = 2 Application.ReplaceFormat.Orientation = 90 Selection.Replace What:="Crystalised", Replacement:="Crystalised", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 10 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="H", Replacement:="H", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 45 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="MH", Replacement:="MH", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 6 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="ML", Replacement:="ML", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 4 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="L", Replacement:="L", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Replaceformat problem
It's working fine now.
"davethewelder" wrote: I have a macro which worked perfectly this morning which replaced values with the same value and replaced the interior colour depending on the value within the cell. I added a select statement with the with the selection of "" to be replaced by "" and the search to be xlNone. This did not produce the desired result and so I removed this piece of code. The problem now is that no colour changes occur in the spreadsheet although the code is the same. Has anyone came across this problem? I have added the relevant code below. Thanks in advance Davie Sub EDITcolumnSCORES() Columns("h:m").Select Selection.ColumnWidth = 8 Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With Application.ReplaceFormat.Interior.ColorIndex = 1 Application.ReplaceFormat.Font.ColorIndex = 2 Application.ReplaceFormat.Orientation = 90 Selection.Replace What:="Crystalised", Replacement:="Crystalised", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 10 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="H", Replacement:="H", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 45 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="MH", Replacement:="MH", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 6 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="ML", Replacement:="ML", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True Application.ReplaceFormat.Interior.ColorIndex = 4 Application.ReplaceFormat.Font.ColorIndex = 1 Application.ReplaceFormat.Orientation = 0 Selection.Replace What:="L", Replacement:="L", Lookat:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Linear Mode
