Find in a column and mark in a another column

[Portucale]

Hi,

I am looking to be able to mark with "1" within column A where in Column F the string "Work in Progress" or "Planned" is found, I would appreciate the help in this procedure.

Thanks in advance,

Any help and all the help is very much appreciated

 

[jindon]

VBA?

Sub test()
    With Range("f2", Range("f" & Rows.Count).End(xlUp)).Offset(, -5)
        .Formula = "=rept(1,or(f2={""Work in progress"",""Planned""}))"
    End With
End Sub

 

[Portucale]

VBA?

Sub test()
    With Range("f2", Range("f" & Rows.Count).End(xlUp)).Offset(, -5)
        .Formula = "=rept(1,or(f2={""Work in progress"",""Planned""}))"
    End With
End Sub

Thanks jindon,

Your solution work great. Can be added that for every time the string is found mark with 1 and font colour is White?
My stakeholder is very demanding.

Many thanks again for the help

 

[jindon]

White?

Sub test()
    With Range("f2", Range("f" & Rows.Count).End(xlUp)).Offset(, -5)
        .Formula = "=rept(""1"",or(f2={""Work in progress"",""Planned""}))"
        .FormatConditions.Delete
        .FormatConditions.Add 2, , "=a2=""1"""
        .FormatConditions(1).Font.Color = vbWhite
    End With
End Sub

 

[Portucale]

White?

Sub test()
    With Range("f2", Range("f" & Rows.Count).End(xlUp)).Offset(, -5)
        .Formula = "=rept(""1"",or(f2={""Work in progress"",""Planned""}))"
        .FormatConditions.Delete
        .FormatConditions.Add 2, , "=a2=""1"""
        .FormatConditions(1).Font.Color = vbWhite
    End With
End Sub

Hi jindon,

Many thanks for your solution, is very interesting to read, and yes I do understand it and will be using it, however and as another solution this is what I did while waiting

Sub scratch1()
' Routine which find a string within a cell (Range)
' and print a value in another cell
' in the example finds Work in Progress or Planned in column F (6)
' and print "1" in column A (-5) in white font
' if string no found than does nothing Value = "" and font colour is Black


    For Each Cell In Range(Cells(2, 6), Cells(2, 6).End(xlDown))
        If Cell = "Work in Progress" Or _
            Cell = "Planned" Then
    Cell.Offset(0, -5) = 1
    Cell.Offset(0, -5).Font.Color = vbWhite
        Else
    Cell.Offset(0, -5).Value = ""
    Cell.Offset(0, -5).Font.Color = vbBlack
        End If
    Next Cell

End Sub

thanks again for your help with this problem,

Kind regards,