MS Excel / VBA help thread

If your data is consistent it should be easy enough. Either use text to columns or

=RIGHT(A1,LEN(A1)-SEARCH("@",A1))

should extract the domain from an email in A1.

Yes that has done what I wanted.

Thanks all

Does anyone know of a function to set a square as a colour if a date is passed and another square has not been changed to completed?

Basically if the status is not updated as completed by the end date on track shows as Red

1 Attachment

Should be able to do this using the formula function within conditional formatting.

As Sam_w says, conditional formatting.

Please don't colour any other columns though. I may get project PTSD.

1 Attachment

Oh one other thing to note =today() works in formulas if you want to compare to current date

I have no use for the colour I however have an MD who likes red/green and he both pays me monthly and hopefully pays my bonus so red/green it is.

Coloured in spreadsheets are the bane of my life. Along with merged column headers....

Merged column headers are the worst, especially when centre across selection exists

If you want over-engineered, overly-coloured tracking spreadsheets, then I'm your man.

Nested if statements within conditional formatting, on cells that are nested if statements, to decide if the tick or cross should be green, red or yellow.

I mean he could just look at the ******** boxes either side to see the end date and if its completed.

Apparently engineers now need project management trackers for small ultimately menial projects they do. Who knew.

Worth also noting just apply the conditional formatting to the specific cells in the table that need it, it will kill excel if you apply it to an entire column

I have a macro that opens every file in a folder and removes any merging.

And then emails the person that did the merging to call them a twat.

I am in awe

Many years ago I did weekly ad reporting that required me to manually download a lot of files. Even selecting the 'raw' option gave me an xls with multiple merged column headers.

Vertically merged cells are actually worse

Also, why do most people add black cell borders? I can't stand it. Excel already provides minimal grey borders. But I genuinely want to know why people do it. Any ideas?

Oh, and leaving col A and row 1 blank to create a margin. What's that about?

I also hate macros.. where did that hard coded value come from?
Cntrl [ functionality.

Edit to add.. why do people use macros when PQ exists within Excel?

Edit to add.. why do people use macros when PQ exists within Excel?

I am stuck in the a place that still runs 2013 and won't enable add-ins, change to 365 tomorrow though

why do most people add black cell borders?

If it isn't a Table with banded rows and header, I use black borders for legibility if there are too many rows that make it hard distinguish reading across

Worth also noting just apply the conditional formatting to the specific cells in the table that need it, it will kill excel if you apply it to an entire column

Urgh, someone some how applied too much formatting to a spreadsheet this week and turned the file from 30mb to 400mb, contains around 60 worksheets as well, so was a nightmare to track down where the size had come from but turned out it was excess formatting on one worksheet in the end

PQ is a relative recent reveal to government wage slaves like me.

Speaking of being a government wage slave, I want a new job again.

I use black borders for legibility

I honestly find black borders really distracting, as they are the same colour as the cell values. The built-in grey borders are ideal for me. And often when reading a row across, I'll select it.

I only use them for tables going in a powerpoint or email tbf and never apply a complete grid of them, just borders around important rows or columns to call out what to focus on, simliar to make the row bold or italic

why do people use macros when PQ exists within Excel

They do very different things. I've got macros to add corporate branding on headers, format pivot tables how I like them, google search the value in the current cell, generate invoices and email companies their outstanding balances, etc

400MB of empty cells, you mean.

vis. macros:

Private Sub Workbook_Open()
    set myRange = all.of.the.useless.empty.cells.that.are.there.because.some.cunt.doesn't.know how.to.excel
    myRange.EntireRow.delete
    myRange.EntireColumn.delete
End Sub