Forum Discussion

Copper Contributor

Apr 06, 2020

VBA To Highlight Rows

Hi We have a piece of software that outputs an Excel file once a week, this file has to be organised and edited to show specific information, which is quite a tedious task! I am trying to produc...

excel

Macros and VBA

TattyJJ

Copper Contributor

Apr 06, 2020

mtarler

Thanks, that seems to have at least get it highlighting just the table.

The are no breaks in the data, other than they are not consecutive.

Quick screen shot of the data:

Conditional formatting won't work as it is a new spreadsheet that is generated every week and has to be altered to produce a report.

mtarler

Silver Contributor

Apr 06, 2020

looks like you are from Europe with that day/month/year format. Is there any chance the comparison is not treating it as a date value but a text value instead?

TattyJJ
Copper Contributor
Apr 06, 2020
mtarler

That is a good point, but to be honest i haven't a clue how to resolve it.
- mtarler
  Silver Contributor
  Apr 06, 2020
  TattyJJ in VBA editor there is an "Immediate Window" that I love to test and try things. If it isn't already up you can find it under the View tab or hit Ctrl-G.
  In the window type something like: Print isdate(range("e20").value)
  - TattyJJ
    Copper Contributor
    Apr 06, 2020
    mtarler
    
    This has got me quite stumped! So by changing TDateM = Date to TDateM = Date + 12 it still works fine until it gets to a different month. If it was some sort of confusion between date formats this should have caused it to trip up?
    
    Print isdate(range("e20").value) comes back as True for all the cells i tried in column E