Cody Vance
asked on
VBA - Find Names, Format, and Change Value
Hi Experts,
I am writing a Macro and need a little help. Right now I used the attached code and just re-add for new names. It finds the "Name" in Column G then for that row where the name is found makes Column A Bold, Replaces Value in Column E with "ST FirstName" (FirstName being the First Name of the person in Column G), and changes Column F to "Please send for Check in.". If the Name being searched was John Smith, it would replace the value in Column E with ST John. I would like to have a sheet named "Names" with Column A being the full name to search for, and Column B being the value to replace in Column E. How can I accomplish this with the code below?
Thank You!
Cody-
I am writing a Macro and need a little help. Right now I used the attached code and just re-add for new names. It finds the "Name" in Column G then for that row where the name is found makes Column A Bold, Replaces Value in Column E with "ST FirstName" (FirstName being the First Name of the person in Column G), and changes Column F to "Please send for Check in.". If the Name being searched was John Smith, it would replace the value in Column E with ST John. I would like to have a sheet named "Names" with Column A being the full name to search for, and Column B being the value to replace in Column E. How can I accomplish this with the code below?
Thank You!
Cody-
For Each Row In ActiveSheet.UsedRange.EntireRow.Rows
If Row.Cells(1, 7).Value = "Name" Then
With Row.Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 15
End With
With Row.Range("E1")
.Value = "ST FirstName"
.Interior.ColorIndex = 15
End With
With Row.Range("F1")
.Value = "Please Send for check in."
.Font.Bold = True
End With
End If
Next Row
ASKER
Hi Dave,
So this is somewhat what I was looking for, but a bit off. I realize now my explanation was a bit off also. It needs to look for names in the "Data" sheet Column G and then make the change in that sheet if it finds names form the "Names" sheet Column A. It should make the changes to the "Data" sheet (i.e. bold, gray fill) and replace Column E value with the value in Column B of "Names" sheet next to the name it found.
Hope this makes sense...
Cody-
So this is somewhat what I was looking for, but a bit off. I realize now my explanation was a bit off also. It needs to look for names in the "Data" sheet Column G and then make the change in that sheet if it finds names form the "Names" sheet Column A. It should make the changes to the "Data" sheet (i.e. bold, gray fill) and replace Column E value with the value in Column B of "Names" sheet next to the name it found.
Hope this makes sense...
Cody-
Could you provide a sample workbook to work with so we can do this in one more go?
Dave
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked perfect! Sorry for not sending with sample...
Cody-
Cody-
ASKER
Hope youre still around.. I just noticed that it only formats the first name found in the Data sheet, then does nothing for the rest. I am attaching sample data this time. It should format each row in "Macro Completed" sheet where name found in the "Names" sheet in Column G. You can see some of your code I edited as it does not need to bold it on the "Names" sheet.
Macro-Sample.xls
Macro-Sample.xls
Try renaming your "Paste Here" tab as "Data", as you specified in your requirements.
Cheers,
Dave
Cheers,
Dave
Here's the code, for a public module:
Open in new window
See attached demo workbook. Just click the macro button to see it work.
Enjoy!
Dave
findNames-r1.xlsm