Posts by abes


    I am trying to determine how I can conditional format (Border thin line) a range of cells that are not blank.

    What I have is a setup macro () that provides data from a Pivot table, formats the 4 columns of data (text in title row, bold for title row, width for each column) and then splits the data into 2 lots of 4 columns (for printing names and phone numbers and printing on complete page) however at the end of the macro I would like to add clearing of any formatting in this area, and then conditional formatting of thin border to every cell with data in it.

    I can do it with the manual conditional formatting - with a dynamic formula =$H1>0 (although the cells contain letters) and apply it to $H$1:$K$1000 and $M1:$P$1000 (which fit into the page for printing) but I cannot work out how to add this to the setup macro.

    One of the issues I have is that the macro for arranging the data includes the deletion of 2 columns which confuses the manual conditional format I set, and requires me to set it all up again, hence the automation request.

    Thanks in advance for any assistance:).


    Well some further trials has narrowed the problem down to a pivot table (on sheet 'Receipts') which is driven by a macro. All the macro is supposed to do is unprotect the sheet, refresh the pivot table, protect the sheet and save the workbook. Somehow the format is being applied across the workbook. I have tried changing the field settings value of the pivot table 'sub total' to currency however clicking the macro button and closing does nothing to change the problem. I have found that if I apply the field setting of currency to the pivot table (and anywhere else in the book), then manually refresh the pivot table, manually protect the sheet and then manually save the workbook - once I reopen everything is fine. It appears to be totally linked to the macro but I cannot see why.

    Attached is a copy of the workbook


    A workbook I have is for a Punters Club and is basically the same as the first one I created about 13 years ago, with additions as I have gone along to automate stuff. The code (where I believe the issue I have stems from) was not affecting anything until around 24 months or so ago (maybe a bit longer) when all of a suddenly the number format for the entire workbook changed (in currency type cells) from Currency ($1234.00 , -$1234.00) to Custom ($1234.00, ($1234.00)). I have a feeling this issue became so during Excel 2013. I have tried it on different computers, with excel 2013 & 2016 but no difference. Now I have changed all the numbers back to Currency (as I want) but as soon as I re-open the workbook, (after saving from previous changes) the ($*****) format returns. It now appears there is something in the code (attached) that seems to be forcing a change to the Custom format, however, if so, it never used to. The code was designed so as to write a dollar and cent figure into words for ‘cheques’ I ‘present’ at the end of the year (the formula is =spellnumber(cell) for each of the 'cheques' required). These cheques are on 1 worksheet only, and reference another couple of sheets to get the figures/dates etc.

    I have commented the code out in the spreadsheet, changed the format of one number, saved, closed and re-opened with the currency format remaining – so it appears it is the code making the change (although the code is only a function and not a sub routine). But I cannot read (or completely understand) where the code is now forcing a change of the entire workbook rather than the one sheet it should be. I would appreciate of someone could advise where in the code the change is called, and how I would go about changing the format ‘back’ to currency (or not changing the format on all sheets).

    Thanks in advance for any assistance.