Check If not IsNumeric

[micko1]

Trying to get a macro to check if cells A1-A65536 contain text. If so then place a formula in coresponding row column "O" Formula is =IF(OR(F7<=TODAY(),G7<=TODAY(),H7<=TODAY()),"NonCompliant","Compliant")
Thanks in advance.

Mick

 

[micko1]

Tried that but then get the error "Application Defined or Object Defined Error" and stops on this line.

"SetCF .Range("F:H").Resize(UBound(a) - 6).Offset(6)"

 

[ZVI]

To check from row 7 try:

Sub Test3()
  
  Dim Rng As Range, a, v, r&
  
  ' Set range of values in Column A
  With ActiveSheet
    If .FilterMode Then .ShowAllData
    Set Rng = .Range("A7", .Cells(.Rows.Count, 1).End(xlUp))
  End With
  
  ' Copy Rng values to  a() to speed up processing
  a = Rng.Value
  
  ' Analyze each value of a(), empty it or put formula into it
  For r = 1 To UBound(a)
    v = a(r, 1)
    a(r, 1) = Empty
    If VarType(v) = vbString Then
      If Len(v) > 0 Then
        a(r, 1) = "=IF(MIN(RC[-9]:RC[-7])<=TODAY(),""NonCompliant"",""Compliant"")"
      End If
    End If
  Next
  
  ' Put formulas and CFs to the destination ranges
  With Rng
    ' Copy a() to the destination column O
    .Columns("O").Value = a
    ' Set CFs to F:H range
    SetCF .Columns("F:H")
  End With
  
End Sub


Private Sub SetCF(Rng As Range)
  
  ' Conditional formulas in R1C1 format, change to suit
  Const Fm1$ = "=IF(ISTEXT(RC1),RC6 <= TODAY())"
  Const Fm2$ = "=IF(ISTEXT(RC1),AND(RC > TODAY(),RC6 < TODAY()+7))"
  Const Fm3$ = "=IF(ISTEXT(RC1),AND(RC > TODAY(),RC6 < TODAY()+30))"
  
  With Rng
    With .FormatConditions
      ' Delete CFs
      .Delete
      ' Add CF #1
      With .Add(Type:=xlExpression, Formula1:=Fm1)
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 35 ' <-- Change to suit
      End With
      ' Add CF #2
      With .Add(Type:=xlExpression, Formula1:=Fm2)
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 36 ' <-- Change to suit
      End With
      ' Add CF #3
      With .Add(Type:=xlExpression, Formula1:=Fm3)
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 40 ' <-- Change to suit
      End With
    End With
  End With
End Sub

Regards

 

[micko1]

I do not know how to thankyou. I have been working on this workbook for quite some time now and kept comming up against brick walls. Thankyou very much thats great.

Mick

 

[ZVI]

Mick, "all in all it was just a brick in the wall"
Cheers!
Vlad

 

[micko1]

Vlad
Sorry to be a nuisance but since the last lot of code changes I have added columns between the columns that we conditionaly formated. Originally it was columns ("F:H"). is it possible to change the code so the CF does columns F, H, and J. and misses G,and I.
Thanks again for your assistance
Mick

 

[ZVI]

Mick,

Replace this line of code: SetCF .Columns("F:H")
by these 3 lines:
SetCF .Columns("F")
SetCF .Columns("H")
SetCF .Columns("J")

Regards,
Vlad

 

[micko1]

As easy as that. I tried different things but could not get it thanks once again. Hope this is the end of this one.

Mick