How to darken or lighten an existing fill colour?

[stefanaalten]

I'd like to apply some fill colours to a grid which represents events (in rows) and dates (in columns) to make it easier to distinguish entries.

I have manually applied fill colours to the columns. For the sake of argument, alternating columns are in one of three shades of grey. I would also like to show alternating rows in different shades/colours but without "overwriting" the colour fill applied in the columns. i.e. the rows should take whatever colour is there already (column colour formatting) and either make it a bit lighter or a bit darker. Doing this manually would be a real chore!

Where there are entries in the grid, a conditional formatting rule applies a strong primary colour based on the cell contents and this should "overwrite" whatever colour the cell is, irrespective of row or column.

I don't know how to insert an image here but hopefully I've explained what I'm trying to accomplish.

Can anyone help?

 

[rockclimber112358]

Here's some code, give this a try:

Sub Coloring()
    Dim ni As Integer, nj As Integer, i As Integer, j As Integer
    UsedRange.Select
    ni = Selection.Rows.Count
    nj = Selection.Columns.Count
    For i = 1 To ni
        For j = 1 To nj
            If (i / 3 - WorksheetFunction.Floor(i / 3, 1)) * 3 = 0 Then Cells(i, j).Interior.Color = 60000
            If (i / 3 - WorksheetFunction.Floor(i / 3, 1)) * 3 = 1 Then Cells(i, j).Interior.Color = 40000
            If (i / 3 - WorksheetFunction.Floor(i / 3, 1)) * 3 = 2 Then Cells(i, j).Interior.Color = 50000
            If (j / 3 - WorksheetFunction.Floor(j / 3, 1)) * 3 = 1 Then Cells(i, j).Interior.TintAndShade = 0.1
            If (j / 3 - WorksheetFunction.Floor(j / 3, 1)) * 3 = 2 Then Cells(i, j).Interior.TintAndShade = 0.2
            If (j / 3 - WorksheetFunction.Floor(j / 3, 1)) * 3 = 0 Then Cells(i, j).Interior.TintAndShade = 0.3
        Next j
    Next i
End Sub

You should be able to change the colors to gray or whatever, just change the interior.color = # to the appropriate numbers.