Conditional Formatting

[gearcutter]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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