For the past three weeks, I have been at a suppliers factory testing a project prior to delivery to site. The other day, one of the managers of the other company came over to our group and gave a graduate a challenge, write an excel script to colour each cell in a 10x10 grid a different colour.

I initially laughed, knowing that it is the sort of thing that I could easily do. Then the manager mentioned that there were many ways of doing it but the best he had seen was doing it in three lines.

Well, that sounded like a challenge. I had a good think about how to do it in one line. I thought I had it a couple of times, but I couldn't get it to work. I could, in one line, print all the numbers between 1 and 100 in each cell within the 10x10 grid, and I could, in a different single line, set the format of all the cells within a grid as long as it was the same format. I could not manage to get the cells to have a different format. Eventually I decided to make use of conditional formating and apply a single (conditional) format to the whole grid that would mean that each cell was a slightly different colour from the ones next to it.

[A1:J10] = [Row(A1:J10)*10+Column(A1:J10)-10]
cs = [A1:J10].FormatConditions.AddColorScale(ColorScaleType:=2)

Close

While this did meet the requirements in only 2 lines, u still wasn't satisfied. Then that night I thought I could cheat and just join the two lines together and do it in one line. But if I am cheating and joining lines together, I may as well go back to my first plan an use a loop (three lines) but actually show random colours and not need to put any text or values in the cells:

for each c In [A1:J10]:c.Interior.Color=c.row()*3e5-c.column()*7e6:next

Close

Not only does this look nicer, it uses up less code overall. One line less than 80 characters long. I am still annoyed I have to cheat and join lines together but I don't think I will manage any better.

If anyone does, I would love to know.