Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for finding formatted cells
Columns D to F contain some cells with two diagonal borders forming an X. I
want to find each formatted cell and enter "1" in it. I can do a simple macro using Find but can't figure out how to get it to loop. I'm fairly new to this sort of thing so really need a simple "copy'n'paste" type answer. Thanks for your help -- Linda M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for finding formatted cells
Post the code that works to find 1 cell.
-- Regards, Tom Ogilvy "LindaM" wrote in message ... Columns D to F contain some cells with two diagonal borders forming an X. I want to find each formatted cell and enter "1" in it. I can do a simple macro using Find but can't figure out how to get it to loop. I'm fairly new to this sort of thing so really need a simple "copy'n'paste" type answer. Thanks for your help -- Linda M |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for finding formatted cells
Hi Tom
Thanks for you rapid response. I just used the record macro facility. This is what it gave me: Columns("D:F").Select With Application.FindFormat.Borders(xlDiagonalDown) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Application.FindFormat.Borders(xlDiagonalUp) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=True).Activate ActiveCell.FormulaR1C1 = "1" End Sub -- Linda M "Tom Ogilvy" wrote: Post the code that works to find 1 cell. -- Regards, Tom Ogilvy "LindaM" wrote in message ... Columns D to F contain some cells with two diagonal borders forming an X. I want to find each formatted cell and enter "1" in it. I can do a simple macro using Find but can't figure out how to get it to loop. I'm fairly new to this sort of thing so really need a simple "copy'n'paste" type answer. Thanks for your help -- Linda M |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for finding formatted cells
Sub AABBCC() Dim rng As Range, rng1 As Range Dim sAddr as String Set rng1 = Columns("D:F") With Application.FindFormat.Borders(xlDiagonalDown) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Application.FindFormat.Borders(xlDiagonalUp) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Set rng = rng1.Find(What:="", After:=Range("D1"), _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=True) rng.Select If Not rng Is Nothing Then sAddr = rng.Address Do rng.Value = 1 Set rng = rng1.Find(What:="", After:=rng, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=True) Loop While rng.Address < sAddr End If End Sub worked OK for me. -- regards, Tom Ogilvy -- Regards, Tom Ogilvy ActiveCell.FormulaR1C1 = "1" "LindaM" wrote in message ... Hi Tom Thanks for you rapid response. I just used the record macro facility. This is what it gave me: Columns("D:F").Select With Application.FindFormat.Borders(xlDiagonalDown) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Application.FindFormat.Borders(xlDiagonalUp) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=True).Activate ActiveCell.FormulaR1C1 = "1" End Sub -- Linda M "Tom Ogilvy" wrote: Post the code that works to find 1 cell. -- Regards, Tom Ogilvy "LindaM" wrote in message ... Columns D to F contain some cells with two diagonal borders forming an X. I want to find each formatted cell and enter "1" in it. I can do a simple macro using Find but can't figure out how to get it to loop. I'm fairly new to this sort of thing so really need a simple "copy'n'paste" type answer. Thanks for your help -- Linda M |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for finding formatted cells
Fantastic! Thank you very much
-- Linda M "Tom Ogilvy" wrote: Sub AABBCC() Dim rng As Range, rng1 As Range Dim sAddr as String Set rng1 = Columns("D:F") With Application.FindFormat.Borders(xlDiagonalDown) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Application.FindFormat.Borders(xlDiagonalUp) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Set rng = rng1.Find(What:="", After:=Range("D1"), _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=True) rng.Select If Not rng Is Nothing Then sAddr = rng.Address Do rng.Value = 1 Set rng = rng1.Find(What:="", After:=rng, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=True) Loop While rng.Address < sAddr End If End Sub worked OK for me. -- regards, Tom Ogilvy -- Regards, Tom Ogilvy ActiveCell.FormulaR1C1 = "1" "LindaM" wrote in message ... Hi Tom Thanks for you rapid response. I just used the record macro facility. This is what it gave me: Columns("D:F").Select With Application.FindFormat.Borders(xlDiagonalDown) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Application.FindFormat.Borders(xlDiagonalUp) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=True).Activate ActiveCell.FormulaR1C1 = "1" End Sub -- Linda M "Tom Ogilvy" wrote: Post the code that works to find 1 cell. -- Regards, Tom Ogilvy "LindaM" wrote in message ... Columns D to F contain some cells with two diagonal borders forming an X. I want to find each formatted cell and enter "1" in it. I can do a simple macro using Find but can't figure out how to get it to loop. I'm fairly new to this sort of thing so really need a simple "copy'n'paste" type answer. Thanks for your help -- Linda M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Formatted Cells | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions | |||
Finding a formatted cell | Excel Programming | |||
finding a Color Formatted Cell and then displaying a cell's value in same row?? | Excel Programming | |||
Macro trouble finding 'empty' cells | Excel Programming |