ExcelBanter - Excel Formatting - Think I'll need a macro for this

sorry - just noticed a typo-
Set my2ndRange = Sheet2.Range("a1:a" & r)

should be:

Set my2ndRange = Sheet2.Range("a1:a" & rr)

Susan wrote:
:) you've gone past my abilities now. & i'm having trouble making the
search repeat. what i've got so far is posted below & i will e-mail
you the sample workbook.

someone else will have to take it the rest of the way........... sorry!
susan
xxxxxxxxxxxxxxxxxxxxx
Module 1:
Option Explicit

Sub Format()

Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim cell As Range
Dim r As Long
Dim rr As Long
Dim myRange As Range
Dim my2ndRange As Range
Dim SearchName As String

Set Sheet1 = ThisWorkbook.Worksheets("Sheet1")
Set Sheet2 = ThisWorkbook.Worksheets("Sheet2")

Sheet1.Activate
r = Sheet1.Cells(20000, 1).End(xlUp).Row
Set myRange = Sheet1.Range("a1:a" & r)

With Sheet2
.Activate
rr = Sheet2.Cells(20000, 1).End(xlUp).Row
Set my2ndRange = Sheet2.Range("a1:a" & r)
.Range("a2").Select
SearchName = Sheet2.Range("a2").Value
End With

For Each cell In my2ndRange
On Error Resume Next

If ActiveCell.Text = "" Then
Exit Sub
End If

Sheet1.Select

If myRange.find(What:=SearchName, After:=ActiveCell, LookIn:=xlValues,
_
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False).Activate = False Then

MsgBox "No match for " & SearchName & " found.", vbInformation

Else
Call cell_borders

' Do Until ActiveCell = ""
' If myRange.find(What:=SearchName, After:=ActiveCell,
LookIn:=xlValues, _
' LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
' MatchCase:=False).Activate = False Then
' Exit Do
'
' Else
' Call cell_borders
' End If
' Loop

End If

Sheet1.Range("a2").Select
Sheet2.Select
ActiveCell.Offset(1, 0).Select
SearchName = ActiveCell.Value

Next cell

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxx
Module 2:
Option Explicit

Public Sub cell_borders()

Do While ActiveCell.Value < ""
ActiveCell.Offset(0, 1).Select
With ActiveCell
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

End With
Loop

ActiveCell.End(xlToLeft).End(xlToLeft).Offset(1, 0).Select

End Sub
xxxxxxxxxxxxxxxxxxxxxx

wrote:
Could you possibly email this to me?

my email address is

For the data in sheet 1 column A, if the word in sheet 2 column matches
the word in sheet 1 column A then it will replace the cells along the
row with SUM calculations in the data above that until it finds the
first blank cell.

Kind Regards,

Andrea.

Susan wrote:
yes, you're right, this would only search for the first occurrence of
the word. i can fix it so that it keeps searching for the next
occurrence of the word, after doing the formatting, until it hits a
blank cell. i can always forward the spreadsheet to you if you'd like.

i set up a spreadsheet to test this on with a whole bunch of names on
sheet1 column a, and then some of the same names & some not in that
first column in sheet2 column a. it worked fine for me. it searches
for the values exactly as they are written/shown in the sheet2 column
a.
susan

wrote:
Hi Susan,

Thank you for your reply.

The formula you gave works for one occurence of Test one.

I placed the values Test, Test1, Test2 within sheet 2 column A.

I then placed the same test names in sheet 1 column A and expected the
macro to look for the word in column A. If the word is found then do
the automatic formatting.

Let me know if you can see any errors or if someone else could help
repair this small problem that'd be much appreciated.

Thank you

Andrea

Susan wrote:
hi andrea!
i've been working on this code all afternoon! :) (the day after
t-giving is VERY slow). i'm having some trouble lately with worksheets
& could only get this to work by adding "sheetx.Activate", but it does
work.
won't be able to check on any replies to this over the weekend......
maybe somebody else can help you fix it if you need help. this will
get you started, tho.
i'm sure it could be done "better", but as i said, i'm having my own
troubles with macros! ha ha thanks for the chance to help & learn how
to do something new!
i cheated by assuming that b,c,d,e & f would have something in
them......... formatting will end when the cells are blank, so if you
have blanks that's a problem.
susan
xxxxxxxxxxxxxxxxx
Option Explicit

Sub Format()

Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim myLastRow As Integer
Dim myRange As Range
Dim cell As Range
Dim my2ndRange As Range
Dim my2ndLastRow As Integer
Dim SearchName As String

Set Sheet1 = ActiveWorkbook.Worksheets(1)
Set Sheet2 = ActiveWorkbook.Worksheets(2)

Sheet1.Activate
Set myRange = Sheet1.Range("a:a")

Sheet2.Activate
Set my2ndRange = Sheet2.Range("a:a")
Range("a2").Select
SearchName = Sheet2.Range("a2").Value

For Each cell In my2ndRange
On Error Resume Next

If ActiveCell.Text = "" Then
Exit Sub
End If

Sheet1.Activate

If Sheet1.Range("a2:a15").find(What:=SearchName, After:=ActiveCell,
_
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False).Activate = False Then

MsgBox "No match for " & SearchName & " found.", vbInformation

Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
With ActiveCell
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
End With
Loop
End If
Sheet1.Range("a2").Select
Sheet2.Activate
ActiveCell.Offset(1, 0).Select
SearchName = ActiveCell.Value

Next cell

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

wrote:
I have an excel sheet within a large file that contains one sheet with
a large dump of data another sheet with column with names of things I
want formatted.

I want a macro that will find the words in sheet 2 column A and then
look into sheet 1 and then format the cells next to that word in cells
B, C, D, E and F. I want to double underline the cells and place a
line above the cells.

I'm sure this can be done but I'm a novice to this stuff.

Hope someone can help me,

Thank you

Andrea