time value format

[kelvin_9]

hi all, i would like to ask how can i amend my issue.

A1 is used to enter a time to color B1:B100 by conditional formatting which is greater than A1
i've manual changed the format both are "hh:mm", while i always get stuck in column B with format "date time" like "8/10/2019 20:15", thus i cant get the answer.

thanks all

 

[PCL]

Still lost ...!
If A1 = 20:31 all cells in column B are colored correctly ...!
Please give detailed explanations
You don't answer to previous question:
What is wrong about B7 and B12

 

[kelvin_9]

Still lost ...!
If A1 = 20:31 all cells in column B are colored correctly ...!
Please give detailed explanations
You don't answer to previous question:
What is wrong about B7 and B12

maybe this can help?
i use your formula to try with "20:29, 20:30, 20:31", 20:31 get colored on 20:30 like H7, H12

 

[PCL]

Your display is now clear.
Finally, I got the same issue but refreshing the copy / paste of the format it clean up all issues: No explanation.
The only way to get it robust is to prepare manually the conditional format for each cell in column B and apply the format only for the cell concerned: Long and tedious
So the problem is not the formula but the way to apply it to the full column ...!

 

[PCL]

You could have a look on next link to see how to apply Conditional Format but you certainly already did it right
https://www.dummies.com/software/mi...cel-cells-based-on-the-value-of-another-cell/

 

[kelvin_9]

Your display is now clear.
Finally, I got the same issue but refreshing the copy / paste of the format it clean up all issues: No explanation.
The only way to get it robust is to prepare manually the conditional format for each cell in column B and apply the format only for the cell concerned: Long and tedious
So the problem is not the formula but the way to apply it to the full column ...!

You could have a look on next link to see how to apply Conditional Format but you certainly already did it right
https://www.dummies.com/software/mi...cel-cells-based-on-the-value-of-another-cell/

thanks for the useful link and i really appreciate you helped a lot.

 

[PCL]

In case you did not find a clear answer, here a macro to prepare a conditional format for each cell of the selection.
The cell reference is A1 and the fill color is colorIndex = 3

Sub Treat()
Dim Rg  As Range
Dim Add As String
Const RefAdd = "$A$1"           '  Here the cell in reference


    For Each Rg In Selection
        Add = Rg.Address
        Rg.FormatConditions.Delete
        Rg.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=IF((" & Add & "-INT(" & Add & "))>=(" & RefAdd & "-INT($A$1)),TRUE,FALSE)"
            
        Rg.FormatConditions(1).Interior.ColorIndex = 3          '  Here the color
    Next Rg
End Sub