If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 A Huge Conditional Format Challenge....
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## A Huge Conditional Format Challenge....

#1
August 17th 12, 01:37 PM posted to microsoft.public.excel.programming
 Living the Dream external usenet poster Posts: 93
A Huge Conditional Format Challenge....

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.

P.S.

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

TIA
Mick.

#2
August 23rd 12, 04:44 PM posted to microsoft.public.excel.programming
 Living the Dream external usenet poster Posts: 93
A Huge Conditional Format Challenge....

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

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.
#3
August 29th 12, 10:51 PM posted to microsoft.public.excel.programming
 external usenet poster Posts: 29
A Huge Conditional Format Challenge....

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 the entire 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

Cells.ColumnWidth = 4.14
Cells.HorizontalAlignment = xlCenter

Application.ScreenUpdating = True

End Sub

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Excel 2003 Conditional formatting challenge question John C. Excel Discussion (Misc queries) 11 November 9th 09 05:11 PM Conditional Formatting Challenge JLGWhiz Excel Programming 2 December 13th 08 03:58 AM Challenge with conditional find Vic[_4_] Excel Programming 3 May 13th 07 02:40 AM huge huge excel file... why? Josh Excel Discussion (Misc queries) 12 February 9th 06 09:55 PM conditional formatting challenge brendan Excel Discussion (Misc queries) 2 October 18th 05 08:28 AM

All times are GMT +1. The time now is 04:48 PM.