Using VBA to replace cell values and formatting dependant on the current values within the cell

[MGLolly]

Hi,

I am trying to create a macro that goes through the 'F' column of my worksheet and replaces the text and formatting of each cell dependant on what the cell currently contains.

I am pretty new to using VBA in Excel so I'm not 100% sure what I am doing, but here's what I have so far:

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub ReplaceValues()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myDataRng AsRange[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim cell As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set myDataRng =Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    For Each cell InmyDataRng[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        If InStr(1,cell.Value, "*Tol*", vbTextCompare) = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            cell.Value= Replace(cell.Value, "*Tol*", "Tolerable")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           cell.Fill.Color = vbGreen[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ElseIfInStr(1, cell.Value, "*Sub*", vbTextCompare) = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            cell.Value= Replace(cell.Value, "*Sub*", "Substantial")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            cell.Fill.Color= vbRed[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ElseIfInStr(1, cell.Value, "*Mod*", vbTextCompare) = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            cell.Value= Replace(cell.Value, "*Mod*", "Moderate")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           cell.Fill.Color = vbOrange[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ElseIfInStr(1, cell.Value, "*Int*", vbTextCompare) = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            cell.Value= Replace(cell.Value, "*Int*", "Intolerable")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           cell.Fill.Color = vbRed[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           cell.Font.Color = vbBlack[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Next cell[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Can anyone help me?

 

[Fluff]

Hi & welcome to MrExcel.
What is your question?

 

[MGLolly]

The code I have posted isn't working, what can I do to fix it?

 

[Fluff]

In what way isn't it working?