Conditional Formatting

[honkin]

Hi
I have searched through the existing threads and was unable to find anything to specifically help me.

I wish to conditionally format a range of cells based on two criteria. I have been able to get that to work perfectly, but there is an issue. The file is changed by other staff daily and when this happens, the conditional formatting goes haywire. 

Here is the formula I created to conditionally format cells in column A

=AND(TEXT($B2,"dd/mm/yyyy")=TEXT(TODAY(),"dd/mm/yyyy"), OR($A2="FA_Win_3", $A2="FA_Win_2"))

I had selected the range of A2:A70 which should see everything always included. So if the date in say B2 is today and the adjacent cell in column A has either FA_Win_2 or FA_Win_3, the cell is formatted a particular color.

But what happens is daily, additional data is added and older data removed. When I go back in to look at the conditional formatting rule, there might be three or four rules, with none of them covering the range A2:A70. So I need a way of protecting the conditional formatting of A2:A70 so that regardless what data is pasted in there, the rule applies

Someone suggested using the VBE to handle the task, but I keep getting a compile error in the formula used. This is how it looks:-

Private Sub Worksheet_Change(ByVal Target As Range)

    ' This method has a drawback and is that the undo feature in this sheet no longer works (if you can live with it, no problem)
    ' Here is an option to bring it back: https://www.jkp-ads.com/Articles/UndoWithVBA04.asp

    ' Define variables
    Dim targetRange As Range
    Dim formulaEval As String

    ' Define the Range where they paste the date. This is the range that receives the conditional format
    Set targetRange = Range("B2:B70")

    ' 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

End Sub

Just to explain, the date formate in column B is d/m/yyyy hh:mm so that is why the first part of the formula is the way it is. It all works perfectly when just applied to a range as conditional formatting.

I know that in the above code, the range B2:B70 is showing, but the helper text said to input the cells here where the date is pasted. I have done that rather than the range for the conditional formatting, though the cells to be formatted are A2:A70.

As indicated, my aim is to have cells in column A which meet the criteria be conditionally formatted, regardless of the data input; so long as it meets the criteria, it is coloured.

Any help on getting this to work correctly would be greatly appreciated

regards

[#2]

Hello @honkin !

To get rules in your Column A you can also check out the data rules in "Data" Tab- data tools - search for that icon i inserted you as screenshot.

Within that data review dialogue you have 3 tabs. in 1st tab you can select "user defined" and type in your already created formula.

Then you can switch to 2nd tab and define some information for your user what they must enter in that column. Finally on 3rd tab i recommend to block all other inputs that are not equal to your rule.

Hope that helps. Greets, Eva.

[#3]

Thanks so much for your reply, Eva, but I don't think it is of any use in this instance as it puts restrictions on what can be input in the cells. That is not what is trying to be achieved. The other users in the office already know what to input and there are any number of possibly data inputs to go in each cell. I don't want to restrict what data goes in, just conditionally format what does go in

All that is required is for column A to change colour for any cell which meets the criteria listed in the formula and for this not to change regardless of data pasted into the column; so long as it meets the criteria, the cell changes colour. When I input my formula into the Data Validation section you showed me, absolutely nothing happens, even though there are cells which meet the criteria. Is this technique to be used in conjunction with conditional formatting? Otherwise I am not sure how the cells in column A will change colour.

Thanks for your reply