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

 

[JoeMo]

Assuming that a cell "marked in red" is a cell with red fill - try this:

Sub senget()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1:IZ800")
    If InStr(c.Value, "x") > 5 Then c.Interior.Color = vbRed
Next c
Application.ScreenUpdating = True
End Sub

 

[senget]

thank you sir, its very helpfull

Assuming that a cell "marked in red" is a cell with red fill - try this:

Sub senget()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1:IZ800")
    If InStr(c.Value, "x") > 5 Then c.Interior.Color = vbRed
Next c
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another way to do it (should be quick fast) without having to loop through all of the cell...

[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]

 

[JoeMo]

Here is another way to do it (should be quick fast) without having to loop through all of the cell...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MoreThanFourBeforeX()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbRed
  Range("A1:IZ800").Replace "?????*x*", [COLOR=#ff0000][B]""[/B][/COLOR], xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Rick, why is it that if the replacement string is anything other than the null string ("") the "????*x*" string is replaced, but in the case of the null string "????*x*" is NOT replaced by ""?

 

[Rick Rothstein]

Rick, why is it that if the replacement string is anything other than the null string ("") the "????*x*" string is replaced, but in the case of the null string "????*x*" is NOT replaced by ""?

To tell you the truth, I am not 100% sure, but my observation is that when you specify the ReplaceFormat argument as True and leave the ReplaceWith argument blank (set it to the empty string ""), the cells identified by the FindWhat field only get the specified "replacement" format applied and the original data is left alone.

 

[JoeMo]

To tell you the truth, I am not 100% sure, but my observation is that when you specify the ReplaceFormat argument as True and leave the ReplaceWith argument blank (set it to the empty string ""), the cells identified by the FindWhat field only get the specified "replacement" format applied and the original data is left alone.

That's what happens, but you'd never know it from reading the Microsoft documentation for the Range.Replace Method! Thanks for sharing that. It's a neat trick.

 

[Rick Rothstein]

That's what happens, but you'd never know it from reading the Microsoft documentation for the Range.Replace Method! Thanks for sharing that. It's a neat trick.

Yes, it is a neat trick with lots of useful applications. As I remember it, I "discovered" the trick a few years ago by accident when I went to delete the contents of certain cells while trying to fill the supposed resulting blanks with some color... the color worked, but the cells did not blank out. That started me on an investigation which led to the conclusion I posted. By the way, you can leave the "Find what" argument blank and specify True for the SearchFormat argument in order to find cells of a certain format whether the cells have data in them or not. For example, fill a mix of scattered cells with the Red color... put text in some of those red cells and leave other blank. Then execute this code in the Immediate Window and watch all of the red cells become green without affecting their actual content (if you put text into the Replacement argument, the color swap will take place and all cells will fill with the new text)...

Sub MakeRedCellsGreen()
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.Color = vbRed
    .ReplaceFormat.Interior.Color = vbGreen
    Cells.Replace "", "", , , , , True, True
    .FindFormat.Clear
    .ReplaceFormat.Clear
  End With
End Sub

 

[Marcelo Branco]

@Rick

Thank you for sharing this trick

M.

 

[Marcelo Branco]

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?

M.