The 2nd Case is just an example. Add as many Cases as you need.
Option Explicit
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
Public glb_origCalculationMode As Integer
Sub testKen()
'1. Blank cells
'2. Cells with certain value, suach as "0", "XYZ"
'3. When the value of other cells, let say, the cell in the same row in column A, is "0", _
start with "XZY", equal to "XZY", or end with "XZY".
'4. The activecell is blank while the value of other cells, let say, _
the cell in the same row in column A, is "0", start with "XZY", equal to "XZY", or end with "XZY".
Dim cell As Range
On Error GoTo EndSub
SpeedOn
For Each cell In Range("B3", Range("B" & Rows.Count).End(xlUp))
Select Case True
Case Left(Range("A" & cell.Row).Value2, 3) = "TCI" And IsEmpty(cell)
cell.Value2 = "XYZ"
Case Right(Range("A" & cell.Row).Value2, 3) = "XYZ" And IsEmpty(cell)
cell.Value2 = "Right 3 is XYZ"
Case Else
'something if nothing is true
End Select
Next cell
EndSub:
SpeedOff
End Sub
Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub
Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub