dates, columns, conditional formatting, neighbouring cells (below/above)

[MarkoCroatia]

Hello everybody out there,

I do believe that VBA code is needed for my problem. So please, be kind and help me.

I have table with many dates (all dates are in one specific column).
Cells with dates are filled without some special order.
Filtering is not applicable, because of other data (data in other columns).

What is needed (Problem):

• Is there a way to compare dates of neighbouring cells (cell above/below),
• If dates are same, then copy conditional formatting from 1 cell in column with same date,
• Conditional formatting changes only in case of a different neghbouring dates,

For example:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Column (date) I conditional formatting interior cell color

A I (CF)

25.11.2017 I blue

26.11.2017 I red

26.11.2017 I red

26.11.2017 I red

22.11.2017 I blue

22.11.2017 I blue

25.11.2017 I red

etc. I etc.

</code>I am familiar with VBA, so VBA code would be great. I do believe, Private Sub is needed here.

Many thanks in advance.

Greetings from Croatia

Marko

 

[Fluff]

If you are happy to use a helper column, this can be done with CF.
I've used Col N as the helper & Col E with the dates.
In N2 put =IF(E2=E1,N1,1-N1) and copy down.
then you can use CF on col E create a CF using the formula =$N1=1 & then select the format you want

 

[MarkoCroatia]

If you are happy to use a helper column, this can be done with CF.
I've used Col N as the helper & Col E with the dates.
In N2 put =IF(E2=E1,N1,1-N1) and copy down.
then you can use CF on col E create a CF using the formula =$N1=1 & then select the format you want

Hello Fluff,
Man called QHarr alredy helped me with his code.
After small corrections, code is given bellow.
All credits goes to QHarr.
Anyway many thanks for Your suggestion.
Regards,
Marko


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Public Sub ColourCells()

Dim wb As Workbook
Dim wsSource As Worksheet

Set wb = ThisWorkbook
Set wsSource = wb.Worksheets("Sheet1")

Dim lastRow As Long

lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

Dim loopRange As Range

Set loopRange = wsSource.Range("A1:A" & lastRow) ' adjust here if starts elsewhere

Dim currValue As Variant
Dim cellColor As String

cellColor = vbBlue

Dim currCell As Range

For Each currCell In loopRange.Rows

If Not IsEmpty(currCell) Then

If currCell.Row = 1 Then
currCell.Font.Color = cellColor
currValue = currCell.Value2

ElseIf currCell = currValue Then
currCell.Font.Color = cellColor

Else
If cellColor = vbBlue Then
cellColor = vbRed
Else
cellColor = vbBlue
End If

currCell.Font.Color = cellColor
currValue = currCell.Value2

End If

End If

Next currCell

End sub</code>