Selected Answer
Yes, your use of Find/Replace is correct. Your formula looks for the wild card "*" (meaning "anything") and replaces it with whatever word you specify. You could use exactly the same code to replace "Block" with "No plan" or "2" with "Not in plan". But be carfeful of the difference between 2 and "2". The first is a number, the other a letter. Excel can't find 2 if you specify "2".
Of course, Replace can't change an adjacent cell. For that you need code as explained below.
Option Explicit
' make it a habit to always add this option
' at the top of every code module
Sub ReplaceText3()
' know what you are looking at!! Declare all your variables
' If you don't know the data type use "As Variant"
' or "As Object" if you know that the type is an object
Dim Cell As Range ' loop object
' Loop through all cells in the specified range
For Each Cell In Range("A1:A16")
If Cell.Value = 2 Then
Cell.Offset(0, 1).Value = "Not in plan"
End If
Next Cell
End Sub
The code takes each cell of the range in turn, one after the other. So the loop will run 16 times in the above example. Each time the variable Cell refers to a different cell starting with A1. Cell.Offset(0, 1).Value = "Not in plan" specifies the cell offset from Cell by 0 rows and 1 column. You could change this specs to specify another cell in the same row as Cell or another row at a fixed distance from it.
Observe If Cell.Value = 2 specifies the number 2. If you formatted A1:A16 as Text the number won't be found but If Cell.Value = "2" will work. This kind of formula will be very useful to you in your project. It can be adapted to many situations.
The code below contains a host of other useful ideas. For one, it's an Event Procedure. It responds to a change made to any cell of a worksheet. A standard code module, like Module1, won't know what happened on a worksheet. Therefore the code must be pasted to the code module that has the name of one of your wortksheets. These modules are set up by Excel when you create a worksheet and they have all the necessary connections already. Paste the code below to the code module of the worksheet from which you copied your sample. (Observe the Option specified at the top).
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' This procedure runs when any cell in the worksheet is modified
' "Target" is the cell that was modified (supplied by Excel)
' but you want to consider action only if the change
' occurred in this range:-
Const TriggerRange As String = "B1:B16"
Dim Col As Long ' a colour number
' check: Is "Target" within the "TriggerRange"?
' (Jump right to the end if the change was elsewhere)
If Not Application.Intersect(Target, Range(TriggerRange)) Is Nothing Then
' examine the value of "Target"
' assign a different colour number to the variable 'Col' depending upon the Value
Select Case Target.Value
Case "Hold"
Col = 12611584
Case "Pass"
Col = vbGreen
Case "Fail"
Col = vbRed
Case "Pending"
Col = vbBlue
Case "Not in plan"
Col = 15773696
Case "Block"
Col = vbYellow
Case Else
' if none of the words matches specify no colour
Col = 0
End Select
' If a colour was specified above Then change the cell colour
If Col <> 0 Then Target.Interior.Color = Col
End If
End Sub
I've added a lot of explanations for you but you may also want to take note of the fact that, at its core, the Select statement is just a more efficient concatenation of many If/Else statements.
To make best use of this procedure you will have to learn the use the VB Editor's Immediate Pane. It should be visible at the bottom of your Editor's screen. If it isn't you can add it by pressing Ctl+G or by selecting it from the View menu where it is [falsely] listed as Immediate Window. This pane is extremely useful for programming. You can let your code write to it with Debug.Print "Hello" or Debug.Print Cell.Value. You can also enter Debug.Print vbYellow directly into the Immediate Pane and it will give you the number 65535 upon [Enter]. There is a shortcut for "Debug.Print" which is a simple questionmark. ? vbyellow will also return 65535 but this won't work when used in a VBA procedure.
From the last example you learn that constants like vbRed, vbYellow, vbBlue, vbBlack etc. are, in fact, numbers - enumerations to be precise (named numbers). You can enter (in the Immediate Pane) ActiveCell.Interior.Color = vbYellow and the cell you selected will turn yellow. You can enter ? ActiveCell.Interior.Color and the pane will show you 65535 upon [Enter]. In this way you can find out the colour you set for any cell and use that number in the above code to apply the same colour to other cells.
Play with this procedure. Modify it so that the font colour will change to white for some of the dark colours and remain black for light colours, changing in synch with the background as ifferent text is entered. The extra syntax you need for this is Target.Font.Color = vbBlack / vbWhite.
I should add that If Col <> 0 Then Target.Interior.Color = Col omits the End If I showed you in my first snippet. You could change this code to
[CODE]If Col <> 0 Then
Target.Interior.Color = Col
End If/CODE]
With this structure you can add more line of code that should only be executed when the condition is met.