cell - Excel: automatic line color according to entry


Keywords:excel 


Question: 

I have a big Excel file that I am using to collect data from multiple sources about some Events (let's call them Event1, Event2, Event3...). Since multiple sources can provide information about the same Event, for each source referring to the same Event I insert a new line and a new ID (the image below provides an example: I have multiple sources describing Event1, so I sort them using different IDs, i.e. 1.1, 1.2, ...)

To facilitate the visualization of these data, I would like to color the background of all the entries referring to the same Event with the same color, as shown in the image below. For example, all the lines whose first cell has the integer 1 should be grey, all those with the integer 2 should be pink and so on. However, I did not find any function or command to automatically do this. Is there a method to do this in Excel automatically?

Example


3 Answers: 

You should be able to use this method upto step 3:

In Step 3, you need to put in a formula specific to your needs. I haven't tested these but either should work: =SEARCH("1", A1)>1 (where A1 is the cell reference to your cell in the Entry ID column) - what you are saying here is find/match any cell that starts with the value 1 =IF(LEFT(A1,1)="1") (where A1 is the cell reference to your cell in the Entry ID column) - what you are saying here is find the left most 1 character in cell A1 and if that is 1 then it matches.

References:

If Cell Starts with Text String... Formula

 

It depends a bit on the number of events and whether you would like to define the colours for each event yourself. If there are a small number of events, you could consider adding conditional formatting for each event separately like Jorg suggested. That will enable you to set the colour scheme yourself. In the event that there are many events, you could consider adding a helper column with the following formula =VALUE(LEFT(A1;FIND(".";A1)-1)). Based on this column you could add conditional formatting for the rows by the use of colour scales. Downside of this method is that Excel determines the colours for you. Alternatively, you could define a list of colours in desired order in VBA, loop through each of the normalised event id's, and set the background colour respectively.

 

You could also use a macro that checks the values in column A and assigns a new color if the entry ID is changed to the next number:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row > 1 Then
    If IsNumeric(Target) Then
        If Target.Column = 1 Then
            If Application.WorksheetFunction.RoundDown(Target.Value, 0) = WorksheetFunction.RoundDown(Cells(Target.Row - 1, 1).Value, 0) + 1 Then

                    r = WorksheetFunction.RandBetween(0, 255)
                    g = WorksheetFunction.RandBetween(0, 255)
                    b = WorksheetFunction.RandBetween(0, 255)
                    Range(Cells(Target.Row, 1), Cells(Target.Row, 3)).Interior.Color = RGB(r, g, b)
            Else
                    Range(Cells(Target.Row - 1, 1), Cells(Target.Row - 1, 3)).Copy
                    Range(Cells(Target.Row, 1), Cells(Target.Row, 3)).PasteSpecial (xlPasteFormats)
                    Application.CutCopyMode = False
            End If
        End If
    End If
End If

End Sub