A Huge Conditional Format Challenge....

[Living the Dream]

Hi everyone

Have I got a challenge for you.

Sheet! Map

In the matrix below, it is represented as:

Column C - Row 1 = ( Vine 72 of Row 1 )
Column G - Row 1 = ( Vine 72 of Row 2 )

Each row counts down to 1, although not all Rows have 72 Vines, some
have less, but that is not as important as anything the code inlays can
be deleted.

As you can also see, every 4th column is blank as it is used as a spacer.

A B C D E F G H
1 1 2
2 BT DE HG BT DE HG
3 R1 72 R2 R1 72 R2
4 RL ST TW RL ST TW

Sheet! Data

Column A = Row ( as in Vine Row, not Excel Row. )
Column B = Variety
Column C = [ RL - Red Leaf ]
Column D = [ DE - Dead ]
Column E = [ R1 - Replant 1 ]
Column F = [ R2 - Replant 2 ]
Column H = [ TW - Trunk Wound ]
Column I = [ HG - High Graft ]
Column J = [ BT - Bent Trunk ]
Column K = [ ST - Small Trunk ]

Ok, now the fun part in attempting to explain this...

As per the example of Sheet! Map there is approx 20,000 cells that make
up the matrix of this particular Vineyard.

In order to get a graphical view of specific conditions affecting non
specific areas throughout the Matrix, i decided to make a cluster of the
8 conditions for each of the Vine Rows.

Here's where it gets mountainous, each, individual cell's Conditional
Format in the Matrix on Sheets! Map points to a single specific Cell on
Sheets! Data, so you can see the anxiety of having to set the CF for
each Cell as it would take a very long time to set the 20K cells. Having
said that.! once this process is done, you would never have to repeat it
as this would become the master template for all repeating years.

In the example Sheet! Map above you will notice this focuses on Vine/Row
72, surrounding it is the 8 conditions that will affect it, if any of
the conditions match from the Data sheet then the corresponding
condition as described above would change color.

I was hoping that someone could come up with a looping code that would
set the Conditional Format Formula and set the individual color format
then step through all the others, all the while incrementing through and
correctly pointing the corresponding counter cell.

I used the following to insert all the initials into the Map cells which
made it so much quicker than doing the old Copy/Paste...

Sub BT()

Dim i As Long

For i = 2 To 133 Step 3

Cells(i, 2).Value = "BT"

Next i

End Sub


As you can see, I stepped 3 cells at a time and was hoping this can be
adopted so that I could step each corresponding "BT" cell on the Map
Sheet, yet at the same time only step 1 cell in the CF Formula.

Anyone brave enough to help in this challenge has my gratitude and awe.

Heap of thanks in advance, I look forward to any comments.

P.S.

Hang in there Rob, we still may be able to conquer this mountain...

TIA
Mick.

 

[Living the Dream]

Hmm.!

It's been a week and as yet nobody has taken up this challenge which
leads me to think that maybe the mountain is indeed, too high.

Can anyone give me a basic pointer in which to set, using VB the formula
for a given cell please.

Correct me if I am wrong please...

With Map!Range("B2")
..ConditionalFormat.Formula = "=IF(Data!J2<>"")"
..Format.BackColor = Light Blue
End With
With Map!Range("C2")
..ConditionalFormat.Formula = "=IF(Data!E2<>"")"
..Format.BackColor = Dark Red
End With
With Map!Range("D2")
..ConditionalFormat.Formula = "=IF(Data!I2<>"")"
..Format.BackColor = Yellow
End With
With Map!Range("B3")
..ConditionalFormat.Formula = "=IF(Data!F2<>"")"
..Format.BackColor = Light Green
End With
With Map!Range("D3")
..ConditionalFormat.Formula = "=IF(Data!G2<>"")"
..Format.BackColor = Dark Green
End With
With Map!Range("B4")
..ConditionalFormat.Formula = "=IF(Data!D2<>"")"
..Format.BackColor = Light Red
End With
With Map!Range("C4")
..ConditionalFormat.Formula = "=IF(Data!K2<>"")"
..Format.BackColor = Dark Blue
End With
With Map!Range("D4")
..ConditionalFormat.Formula = "=IF(Data!H2<>"")"
..Format.BackColor = Orange
End With


Once again

TIA
Mick.

 

[benmcclave]

Good Afternoon,

I'm not quite sure from your description of the problem how to best add conditional formatting to each cell programatically. However, I did find a way to build your matrix with VBA code using a step value of 1 (see below). Now the challenge will be to add conditional formatting.

To that end, you may find it easier to use the row and vine reference numbers in the top row and first column to set up conditional formatting for theentire sheet.

Hope this helps.

Ben

Sub SetupMap()

Dim i As Long
Dim x As Long
Dim lVine As Long
Dim lCols As Long
Dim lRows As Long

Application.ScreenUpdating = False

'number of columns and rows to create
lCols = 36
lRows = 217
i = 0

'First, add row numbers to top
For x = 1 To lCols
If Cells(1, x).Column Mod 4 = 1 Then
i = i + 1
Else
Cells(1, x).Value = i
Cells(1, x).Font.Bold = True
Cells(1, x).Font.Size = 14
End If
Next x

'Then, add vine numbers to first column
lVine = 73
For x = 2 To lRows
Select Case x Mod 3
Case 2
lVine = lVine - 1
Cells(x, 1).Value = lVine
Case Else
Cells(x, 1).Value = lVine
End Select
Next x

'Finally, create each 9-cell matrix
For x = 2 To lCols
lVine = 72
Select Case x Mod 4
Case Is = 1
'Leave blank

Case Is = 2
For i = 2 To lRows
Select Case i Mod 3
Case 0
Cells(i, x).Value = "R1"
Case 1
Cells(i, x).Value = "RL"
Case 2
Cells(i, x).Value = "BT"
End Select
Next i
Case Is = 3
For i = 2 To lRows
Select Case i Mod 3
Case 0
Cells(i, x).Value = lVine
Cells(i, x).Font.Bold = True
Cells(i, x).Font.Size = 14
lVine = lVine - 1
Case 1
Cells(i, x).Value = "ST"
Case 2
Cells(i, x).Value = "DE"
End Select
Next i
Case Is = 0
For i = 2 To lRows
Select Case i Mod 3
Case 0
Cells(i, x).Value = "R2"
Case 1
Cells(i, x).Value = "TW"
Case 2
Cells(i, x).Value = "HG"
End Select
Next i
End Select
Next x

'Format for readability
Cells.ColumnWidth = 4.14
Cells.HorizontalAlignment = xlCenter

Application.ScreenUpdating = True

End Sub