honkin
02-18-2019, 01:45 AM
hi
I need to conditionally format the cells in column A each day. The formatting is based on cells meeting certain criteria; so if B2 is today's date and A2 has either of the following text, FA_Win_2 or FA_Win_3, then A2 is coloured green, the text white and bold.
There was an issue initially, as the format of the cells in column B is d/m/yyyy hh:mm, so I needed my formula to be able to extract the date correctly. I managed to finally get a working formula to format the cells in column A and applied it to the range A2:A70, to make sure it would always cover all of the possible data in that column. The formula was as follows:-
=AND(TEXT($B2,"dd/mm/yyy")=TEXT(TODAY(),"dd/mm/yyyy"), OR($A2="FA_Win_2", "$A2="FA_Win_3"))
This worked perfectly until the following day, when the data in the cells was changed, which happens daily. As matches are played and disappear and new matches are added, the conditional formatting was all over the place. On looking at the conditional formatting applied to the sheet, there might be 3 or 4 rules showing, all covering different ranges to my initial A2:A70, so this was chaos. What worked brilliantly for the first day, fell over as soon as data was replaced.
Someone suggested using VBA and even supplied some code to assist, but I have not been able to get it to do anything, nor get a reply to see what may be wrong.
Again, just to reiterate, all that is required is that when the file is opened on any given day, if the cells in column B are today's date and the text in any of the cells in column A is either FA_Win_2 or FA_Win_3, then those cells are formatted. I just need that to remain and work even when data is deleted and replaced with other data pasted from additional sheets.
So the VBA formula I obtained and modified is below. It lists the range A2:A70, the modified formula and the formatting conditions; color, font color and font weight. I am very new to VBA, so really just replaced the text that was already in the formula with my own. The formula is not the same as the one above, which confused me. There are some instances where there are consecutive "" and so on, but just trusted that it was correct. I am pretty sure there will just be some rather simple syntax errors; actually it gives a compile error when I input it in the VBA section of the sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
' Define variables
Dim targetRange As Range
Dim formulaEval As String
' Define the Range. This is the range that receives the conditional format
Set targetRange = Range("A2:A70")
' Define the formula evaluated by the conditional format (replace ; for ,)
formulaEval = "=AND(TEXT(" B2 ",""dd/mm/yyyy"")=TEXT(TODAY(),""dd/mm/yyyy""), OR(A" 2 "=""FA_Win_3"", A" 2 "=""FA_Win_2""))"
If Not Intersect(Target, targetRange) Is Nothing Then
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:=formulaEval
.FormatConditions(.FormatConditions.Count).SetFirstPriority
' This is where the format applied is defined (you can record a macro and replace the code here)
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(0, 176, 80)
.TintAndShade = 0
.Font.Color = RGB(255, 255, 255)
.Font.Bold = True
End With
.FormatConditions(1).StopIfTrue = False
End With
End If
If someone knows what is wrong with this and why it fails to format the cells in column A, I would be very grateful.
Regards
I need to conditionally format the cells in column A each day. The formatting is based on cells meeting certain criteria; so if B2 is today's date and A2 has either of the following text, FA_Win_2 or FA_Win_3, then A2 is coloured green, the text white and bold.
There was an issue initially, as the format of the cells in column B is d/m/yyyy hh:mm, so I needed my formula to be able to extract the date correctly. I managed to finally get a working formula to format the cells in column A and applied it to the range A2:A70, to make sure it would always cover all of the possible data in that column. The formula was as follows:-
=AND(TEXT($B2,"dd/mm/yyy")=TEXT(TODAY(),"dd/mm/yyyy"), OR($A2="FA_Win_2", "$A2="FA_Win_3"))
This worked perfectly until the following day, when the data in the cells was changed, which happens daily. As matches are played and disappear and new matches are added, the conditional formatting was all over the place. On looking at the conditional formatting applied to the sheet, there might be 3 or 4 rules showing, all covering different ranges to my initial A2:A70, so this was chaos. What worked brilliantly for the first day, fell over as soon as data was replaced.
Someone suggested using VBA and even supplied some code to assist, but I have not been able to get it to do anything, nor get a reply to see what may be wrong.
Again, just to reiterate, all that is required is that when the file is opened on any given day, if the cells in column B are today's date and the text in any of the cells in column A is either FA_Win_2 or FA_Win_3, then those cells are formatted. I just need that to remain and work even when data is deleted and replaced with other data pasted from additional sheets.
So the VBA formula I obtained and modified is below. It lists the range A2:A70, the modified formula and the formatting conditions; color, font color and font weight. I am very new to VBA, so really just replaced the text that was already in the formula with my own. The formula is not the same as the one above, which confused me. There are some instances where there are consecutive "" and so on, but just trusted that it was correct. I am pretty sure there will just be some rather simple syntax errors; actually it gives a compile error when I input it in the VBA section of the sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
' Define variables
Dim targetRange As Range
Dim formulaEval As String
' Define the Range. This is the range that receives the conditional format
Set targetRange = Range("A2:A70")
' Define the formula evaluated by the conditional format (replace ; for ,)
formulaEval = "=AND(TEXT(" B2 ",""dd/mm/yyyy"")=TEXT(TODAY(),""dd/mm/yyyy""), OR(A" 2 "=""FA_Win_3"", A" 2 "=""FA_Win_2""))"
If Not Intersect(Target, targetRange) Is Nothing Then
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:=formulaEval
.FormatConditions(.FormatConditions.Count).SetFirstPriority
' This is where the format applied is defined (you can record a macro and replace the code here)
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(0, 176, 80)
.TintAndShade = 0
.Font.Color = RGB(255, 255, 255)
.Font.Bold = True
End With
.FormatConditions(1).StopIfTrue = False
End With
End If
If someone knows what is wrong with this and why it fails to format the cells in column A, I would be very grateful.
Regards