VBA Excel Find Specific Text in table and Change Font Color

[sanityendshere]

Hey everyone,

I am producing tables from a database that can contain multiple pages of data. One of the report requirements is to change ND to silver or -0349986266670736. I know I could technically do all of this with conditional formatting; however, I have written VBA for all of the other table formattings and was hoping I could add code to change ND to the silver color. I've been looking online and have been unsuccessful to find code that will work for what I need. Is this even possible? If so, will someone help me out with the code for this particular instance? Thanks in advance for any help you can provide.

 

[Logit]

Sorry .. what is ND ?

Are you wanting to change the FONT COLOR or the cell INTERIOR COLOR ?

Post your existing code.

 

[Fluff]

How about

Sub sanityendshere()
   With Application.ReplaceFormat
      .Clear
      .Font.Color = rgbSilver
   End With
   Range("A2:I100").Replace "ND", "ND", xlWhole, , False, , False, True
   Application.ReplaceFormat.Clear
End Sub

 

[sanityendshere]

Please bare with me as I'm very new to VBA and couldn't find my answer in any of the other posts. We use ND to represent when values were non-detect during analysis. Our client would like them to be silver so detected values stand out more in the table. I am hoping to automate this because some of our tables can be 10+ pages.

The code I currently have is:

Sheets(j).Range("C7:" & LastColName & "24").Select
Range("C7:" & LastColName & "24").Activate
With Selection.ReplaceFormat
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
Sheets(j).Range("C7:" & LastColName & "24").Replace "ND", "ND", xlWhole, , False, , False, True
Selection.ReplaceFormat.Clear
End With

 

[Fluff]

Did you try the code I posted?

 

[sanityendshere]

@Fluff I just tried your code and it works! Thank you so much. I think I was trying to make it too difficult with my ranges. THank you!

 

[Fluff]

Glad we could help & thanks for the feedback.