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

MarkoCroatia

New Member
Joined
Nov 26, 2017
Messages
2
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 26, 2017
Messages
2
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,807
Messages
5,638,478
Members
417,026
Latest member
UDK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top