cell marked in red

[senget]

I have a sheet of 800 rows and 260 columns (A1: IZ800) and each cell contains the data are very diverse.
I want to use VBA to look for in each cell the number of characters on the left before the x and if more than 4 characters are given red cell

if VBA is run results:
8654x4289 <- ignored
86x5862 <- ignored
739x19802 <- ignored
34587x231 <- cell marked in red
675368573x525 <- cell marked in red
675368573x525 <- cell marked in red

Any help is greatly appreciated
Thank you in advance

 

[Rick Rothstein]

Rick

I noticed that in your code in post #4 you have set the penultimate parameter, SearchFormat, to False and in post #8 to True.

I tried your code in post #4 with either True or False and it worked in both situations.

Do you know what exactly this parameter means?

I set it out of force of habit. You are right, though, there was no need to set it because I cleared the Application.FindFormat property at the beginning of the code, so there was no search format available to apply, so the SearchFormat argument becomes non-functioning in that case. By the way, for those reading this who are unaware, Application.FindFormat and Application.ReplaceFormat settings are cumulative, meaning each format you set for each of them builds on top of any formats you set earlier for them (whether those prior formats were set by code or via Excel's Find/Replace dialog box) which is why the first thing I did in the code is Clear them.

 

[Rick Rothstein]

I set it out of force of habit. You are right, though, there was no need to set it because I cleared the Application.FindFormat property at the beginning of the code, so there was no search format available to apply, so the SearchFormat argument becomes non-functioning in that case.

Sorry, I just realized I botched the above part of my answer in Message #11 above. Even though you said Message #4, I was looking at my example in Message #8 when I answered your question, so my explanation was not applicable. Let me try again. Here is the code repeated from Message #4...

[table="width: 500"]
[tr]
	[td]Sub MoreThanFourBeforeX()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbRed
  Range("A1:IZ800").Replace "?????*x*", "", xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

The FindFormat and ReplaceFormat settings persist (that is, they are remembered by Excel until changed) whether they were set by code or via Excel's Find/Replace dialog box. If you had not set them previously, then they are still not set and one would not need to specify False for the SearchFormat (or ReplaceFormat) argument. However, if you had previously set the SearchFormat argument (or its equivalent in Excel's Find/Replace dialog box) to True, then the SearchFormat would be automatically remain set to True eve when not specified in the Range.Find statement, hence my deliberately setting it to False so it would be guaranteed not to apply if set. By the way, if the setting for SearchFormat is currently, or if it is deliberately set to, True, but if no format had been specified for it, then it will have no effect on the search (you cannot impose a format that does not exist). I am not sure if the above is as clearly stated as it might be, so I hope is clear to you (the concept and all of its interactions are clear in my mind, but I find putting it in writing is not all that easy).