PDA

View Full Version : Conditional Formatting



gearcutter
11-27-2006, 01:25 PM
Hi Everyone, I'm trying to conditionally format a spreadsheet that contains a Fixture/Results grid. The grid contains dates (27-11-2006) that are British style, which when a result is entered into another spreadsheet, replaces the date with the result in the grid. What I want to do is when a result replaces a date I want the format of the cell to change. I've had numerous attempts at trying to do this over the past few weeks without success I have attached one of the grid and result sheets so that you have some idea what I trying to do. Regards Howard:think:

mdmackillop
11-27-2006, 02:43 PM
Hi Howard
Some impressive formulae, but I think this is a time when VBA is simpler to employ. Here's some worksheet event code to do the work. It's triggered when column U is changed, so be sure to enter Home score first (Checking can be added if required)
Happy to help with any questions
Regards
MD


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Long, rw As Long
Dim Home As Range, Away As Range, Base As Range

If Target.Column <> 21 Then Exit Sub
'Get lists containing teams from Column and Row
Set Home = Sheets(2).Range("Y32:Y39")
Set Away = Sheets(2).Range("AP30:AW30")
'Location of corner cell
Set Base = Sheets(2).Range("AO31")

'Find Row and Column where result is to be entered
rw = Application.WorksheetFunction.Match(Target.Offset(, -6), Home.Value, 0)
col = Application.WorksheetFunction.Match(Target.Offset(, 2), Away.Value, 0)
'Change the cell to Text format
Base.Offset(rw, col).NumberFormat = "@"
'Add the result
Base.Offset(rw, col) = Target.Offset(, -4) & " - " & Target
'Remove apostophes below to go to the cell (testing only)
'Sheets(2).Activate
'Base.Offset(rw, col).Select
End Sub

gearcutter
11-27-2006, 03:01 PM
Hi MD, Thanks for the interest, I'm not too practiced at vba but I will implement your code when I get the time probably tomorrow Thank you , Howard

Hellboy
11-28-2006, 01:38 PM
Hi Howard
Some impressive formulae, but I think this is a time when VBA is simpler to employ. Here's some worksheet event code to do the work. It's triggered when column U is changed, so be sure to enter Home score first (Checking can be added if required)
Happy to help with any questions
Regards
MD


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Long, rw As Long
Dim Home As Range, Away As Range, Base As Range

If Target.Column <> 21 Then Exit Sub
'Get lists containing teams from Column and Row
Set Home = Sheets(2).Range("Y32:Y39")
Set Away = Sheets(2).Range("AP30:AW30")
'Location of corner cell
Set Base = Sheets(2).Range("AO31")

'Find Row and Column where result is to be entered
rw = Application.WorksheetFunction.Match(Target.Offset(, -6), Home.Value, 0)
col = Application.WorksheetFunction.Match(Target.Offset(, 2), Away.Value, 0)
'Change the cell to Text format
Base.Offset(rw, col).NumberFormat = "@"
'Add the result
Base.Offset(rw, col) = Target.Offset(, -4) & " - " & Target
'Remove apostophes below to go to the cell (testing only)
'Sheets(2).Activate
'Base.Offset(rw, col).Select
End Sub



Just for my info

Would this work also ?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .column > 41 and .row > 31 then
If Not IsDate(.Value) Then
.NumberFormatLocal = "@"
End If
End If
End With
End Sub

mdmackillop
11-28-2006, 02:25 PM
Hi Phil,
Not in this case. I'm copying the data from two cells into one cell on another sheet which already contains a date. I nedd to change the format of the latter cell.
Regards
MD

gearcutter
11-28-2006, 02:26 PM
Hi I have tried both codes, and entered acouple of results, the home score first as MD said I should, with MD's code I got error1004 "unable to get the Match property of the worksheetFunction class". With Hellboys code I got no reaction at all. as I said in my previous post I'm not well versed in V.B.A so no doubt I'm doing something wrong somewhere down the line.but thank you for the help. Regards Howard

mdmackillop
11-28-2006, 02:48 PM
I didn't test far enough I see!
Simple problem. The code checks the team names on Sheet Team A with those on Team A Grid. In cell AW30 you abbreviated Serbia & Montenegro by omitting the spaces, hence no match. If you can ensure the names are the same, things should work.
Regards
MD

Hellboy
11-28-2006, 03:13 PM
Hi Phil,
Not in this case. I'm copying the data from two cells into one cell on another sheet which already contains a date. I nedd to change the format of the latter cell.
Regards
MD

Hi

Ok I see.

Thanks !

Phil

gearcutter
11-28-2006, 03:14 PM
Hi MD, I've altered Serbia & Montenegro, Now tell me if I am doing this right. I've put the code in the Workbook proper rather than the worksheets that was posted. The proper workbook has 17 sheets group A is sheet 2 and the grid is on sheet3, so I have altered your code to read sheets(3) is this the correct thing to do ? Now when I enter a score the score goes into the grid but the formula is deleted.Thanks again for your interest Regards Howard

mdmackillop
11-28-2006, 03:44 PM
BTW,
A slight change to the code will fill the list of dates from Group A into the grid


Option Explicit
Sub FillDates()
Dim col As Long, rw As Long
Dim Home As Range, Away As Range, Base As Range
Dim Target As Range, i As Long

For i = 4 To 59
Set Target = Sheets(1).Cells(i, 7)
'Get lists containing teams from Column and Row
Set Home = Sheets(2).Range("Y32:Y39")
Set Away = Sheets(2).Range("AP30:AW30")
'Location of corner cell
Set Base = Sheets(2).Range("AO31")
'Find Row and Column where result is to be entered
rw = Application.WorksheetFunction.Match(Target.Offset(, 8), Home.Value, 0)
col = Application.WorksheetFunction.Match(Target.Offset(, 16), Away.Value, 0)
'Change the cell to Text format
Base.Offset(rw, col).NumberFormat = "d-m-yyyy"
'Add the result
Base.Offset(rw, col) = Target
'Remove apostophes below to go to the cell (testing only)
'Sheets(2).Activate
'Base.Offset(rw, col).Select
Next

End Sub