VBA: How to efficiently search and replace text in multiple worksheets without affecting formatting?
Can someone help me understand I can't seem to get I'm confused about I'm working with an Excel VBA macro that needs to search for specific text across multiple worksheets and replace it with another value... The challenge I'm facing is ensuring that the text replacement doesn't alter the formatting of any cells. I've tried using the `Replace` method on ranges, but it seems to affect the cell formatting when the cell contains different text formats. For example, if a cell has some text bold and some regular, using `Range.Replace` changes the entire cell's formatting to the default. Here's a snippet of what I tried: ```vba Dim ws As Worksheet Dim searchText As String Dim replaceText As String searchText = "oldText" replaceText = "newText" For Each ws In ThisWorkbook.Worksheets ws.Cells.Replace What:=searchText, Replacement:=replaceText, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next ws ``` This results in the formatting being lost, and I need to preserve any bold, italic, or colored text. I also considered looping through each cell to check for the search text, but that seems inefficient and could slow down the process significantly, especially with large datasets. Are there any techniques or best practices to achieve this while maintaining the original formatting? Any insights or alternative approaches would be greatly appreciated! Any ideas how to fix this? I've been using Vba for about a year now. I'd really appreciate any guidance on this. Any pointers in the right direction?