1

I was looking for a vba code that could help me match a filename to a pre-specified format. I want it to color a cell red if it doesnt match the format. For Eg: my format is alrt_OBU_PW_YYYYMMDD_HHMMSS.txt If the file I browse using application.getopenfilename doesnt match this format, it should give me an error.

Thanks!

Here is what i have done till now-

I have copied the filename into a cell. 

FileName = Sheets("Control Sheet").cells(2, "F").Value   
 If FileName = Format("alrt_OBU_PW_" & Format("yyyymmdd") & "_" & Format("hhmmss") & ".txt") Then  
   wqI.range("G21").Interior.ColorIndex = 43  
   Else: wqI.range("G21").Interior.ColorIndex = 3  
End If
kotoj
  • 769
  • 1
  • 6
  • 25
tuls
  • 11
  • 3
  • please show us what have you already done? – kotoj Aug 26 '16 at 08:56
  • I have pasted the filename into a cell.FileName = Sheets("Control Sheet").cells(2, "F").Value If FileName = Format("alrt_OBU_PW_" & Format("yyyymmdd") & "_" & Format("hhmmss") & ".dat") Then wqI.range("G21").Interior.ColorIndex = 43 Else: wqI.range("G21").Interior.ColorIndex = 3 – tuls Aug 26 '16 at 08:58
  • Im very new to VBA, so excuse any mistakes – tuls Aug 26 '16 at 09:00
  • you're not formatting anything Format("yyyymmdd") needs to be Format(now,"yyyymmdd") for example. I think you'll need to split the string and check for date formats, http://stackoverflow.com/questions/15196451/regular-expression-to-validate-datetime-format-mm-dd-yyyy and http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops may assist – Nathan_Sav Aug 26 '16 at 09:09

1 Answers1

0

Please change the Format function with like below one:

Format("alrt_OBU_PW_" & Format(Now(), "yyyymmdd") & "_" & Format(Now(), "hhmmss") & ".txt"

Use correct/exact date & time instead Now().

Balasubramaniyan Ramadoss
  • 146
  • 4