strikethrough

[Haydo]

Is there a way to....
- find (and highlight?) all cells containing - just the three letters - scr
- then strikethrough all text on all rows containing scr ?

Is a vba code best for this or something else?

Thanks in advance

 

[Biz]

Try code below

Sub Test()
    Dim rCells As Range, i As Long
    For Each rCells In Range("A1:A50") '<-- Adjust to suit
        If InStr(LCase(rCells.Value), "scr") <> 0 Then
            With rCells
                .Font.Strikethrough = True
                .Interior.ColorIndex = 3
            End With
        End If
    Next rCells
End Sub

 

[Rick Rothstein]

Here is another way to do what you want (change the column designation to your actual data column)...

Sub Find_scr_StrikethroughIt()
    Application.ReplaceFormat.Font.Strikethrough = True
    Application.ReplaceFormat.Interior.ColorIndex = 3
    Columns("A").Cells.Replace "scr", "scr", xlWhole, , False, , False, True
End Sub

 

[Haydo]

I'm afraid my VBA progression has come to a grinding halt.

I have 5 vba projects.

Where exactly do i paste the above code
and how do i run it?

WHat i did yesterday to get code to run is not working today.

Thanks.

 

[Rick Rothstein]

I'm afraid my VBA progression has come to a grinding halt.

I have 5 vba projects.

Where exactly do i paste the above code
and how do i run it?

You will need to do the following for each workbook (if that is what you meant by "vba projects") that you want to have this functionality. From any worksheet, press ALT+F11 to go into the VB editor. Once there, click Insert/Module on its menu bar. This will open up a code window... copy/paste whichever code procedure you choose to use (I'd suggest using mine as it will execute faster) into that code window. Now go back to your worksheet with the data on it and press ALT+F8 to bring up the macro dialog box. Find the macro name in the list and click the Run button.

 

[Haydo]

Nothing happens.
The excel page is unchanged.

Rick's code - nothing happens

when i run Biz's code, it results in a runtime 13 error.

Any ideas?

 

[Rick Rothstein]

Nothing happens.
The excel page is unchanged.

Rick's code - nothing happens

when i run Biz's code, it results in a runtime 13 error.

Any ideas?

What column (or columns) is the data that can contain "scr" in?

Is your data constants or formulas?

 

[Haydo]

The 'scr' is only in the i column

there only 1 functions applying to one other column.

Will it work with function/formulas?

I will try to copy and paste only the values and see how it goes.

 

[Rick Rothstein]

The 'scr' is only in the i column

Well that is the first problem. You never told us the data is in Column I (you actually did not tell us where it was)... both Biz and I assumed it was in Column A.

there only 1 functions applying to one other column.

I am guessing that means Column I contains constants only.

Give this code a try...

Sub Find_scr_StrikethroughIt()
    Application.ReplaceFormat.Font.Strikethrough = True
    Application.ReplaceFormat.Interior.ColorIndex = 3
    Columns("I").Cells.Replace "scr", "scr", xlWhole, , False, , False, True
End Sub

 

[Haydo]

interesting...
The result is that each of the "scr" cells are coloured red, with black ink, and with strikethrough.
But the remainder of the row is unchanged.

When i said highlight, I meant in the sense that the cells would then be able to be changed all at once, just like when you hold down 'CTRL' and choose multiple cells which are then ready to be changed.

My aim is to have the entire row (containing the "scr") with a strikethrough, but have no colour change.

Thanks for your help with my not so clear instructions....i have to get better at that

Thanks for your help so far.