You are welcome - thanks for the reply.
Private Sub Worksheet_Change(ByVal Target As Range)
Const colorOdd As Long = vbBlue
Const colorEven As Long = vbBlack
Static N() As Long
Dim R As Range, c As Range 'R will be the range you want to monitor for changes
Set R = Range("E4:E12")
ReDim Preserve N(1 To R.Rows.Count)
If Not Intersect(Target, R) Is Nothing Then
For Each c In Intersect(Target, R)
N(c.Row - R(1).Row + 1) = N(c.Row - R(1).Row + 1) + 1
With Range("C" & c.Row, "F" & c.Row)
If N(c.Row - R(1).Row + 1) Mod 2 = 0 Then
With .Font
.Bold = False
.Color = colorEven
End With
Else
With .Font
.Bold = True
.Color = colorOdd
End With
End If
End With
Next c
End If
End Sub
Rewrite_06-05-24.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | |||||||||
2 | 01/01/2024 | ||||||||
3 | NAME | DATE | QTD | PVP | |||||
4 | A | 01/12/2023 | 2 | 5 € | |||||
5 | B | 02/05/2024 | 3 | 8 € | |||||
6 | C | 15/10/2023 | 6 | 3 € | |||||
7 | D | 04/03/2024 | 2 | 10 € | |||||
8 | E | 04/04/2024 | 2 | 30 € | |||||
9 | F | 01/05/2024 | 5 | 14 € | |||||
10 | G | 10/11/2023 | 21 | 21 € | |||||
11 | H | 30/09/2023 | 9 € | ||||||
12 | I | 03/03/2024 | 22 | 11 € | |||||
13 | |||||||||
14 | |||||||||
15 | |||||||||
Folha1 |
I don't understand what you want. In the data that you show in post #22, which cells have CF? If you want the CF color changed even when the CF condition is met then CF must be removed prior to changing the color. Is that what you want?Would like that formula worked with conditional formatting inserted in table despite of the colour chosen in CF would like that returned to the parameterized colour in Formula VBA Code.
Just put first the VBA Formula and then the sheet with table.
Hi @JoeMoI don't understand what you want. In the data that you show in post #22, which cells have CF? If you want the CF color changed even when the CF condition is met then CF must be removed prior to changing the color. Is that what you want?
Rewrite_06-05-24.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | |||||||||
2 | 01/01/2024 | ||||||||
3 | NAME | DATE | QTD | PVP | |||||
4 | A | 01/12/2023 | 2 | 5 € | |||||
5 | B | 02/05/2024 | 3 | 8 € | |||||
6 | C | 15/10/2023 | 6 | 3 € | |||||
7 | D | 04/03/2024 | 5 | 10 € | |||||
8 | E | 04/04/2024 | 2 | 30 € | |||||
9 | F | 01/05/2024 | 5 | 14 € | |||||
10 | G | 10/11/2023 | 21 | 21 € | |||||
11 | H | 30/09/2023 | 3 | 9 € | |||||
12 | I | 03/03/2024 | 22 | 11 € | |||||
13 | |||||||||
14 | |||||||||
Folha1 |
Yes, please give me side by side version. And please include all the CF information that XL2BB can provide for the starting table.If you need an example please let me know can make in another table side by side the goal results.
Dear @JoeMoYes, please give me side by side version. And please include all the CF information that XL2BB can provide for the starting table.
Rewrite_06-05-24.xlsm | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | ||||||||||||||||||
2 | STARTING TABLE | RESULTS AFTER 1ST CHANGE | RESULTS AFTER 2ND CHANGE | |||||||||||||||
3 | 01/01/2024 | 01/01/2024 | 01/01/2024 | |||||||||||||||
4 | NAME | DATE | QTD | PVP | NAME | DATE | QTD | PVP | NAME | DATE | QTD | PVP | ||||||
5 | A | 01/12/2023 | 1 | 5 € | A | 01/12/2023 | 2 | 5 € | A | 01/12/2023 | 3 | 5 € | ||||||
6 | B | 02/05/2024 | 1 | 8 € | B | 02/05/2024 | 1 | 8 € | B | 02/05/2024 | 1 | 8 € | ||||||
7 | C | 15/10/2023 | 1 | 3 € | C | 15/10/2023 | 1 | 3 € | C | 15/10/2023 | 1 | 3 € | ||||||
8 | D | 04/03/2024 | 1 | 10 € | D | 04/03/2024 | 1 | 10 € | D | 04/03/2024 | 1 | 10 € | ||||||
9 | E | 04/04/2024 | 1 | 30 € | E | 04/04/2024 | 2 | 30 € | E | 04/04/2024 | 3 | 30 € | ||||||
10 | F | 01/05/2024 | 1 | 14 € | F | 01/05/2024 | 1 | 14 € | F | 01/05/2024 | 1 | 14 € | ||||||
11 | G | 10/11/2023 | 1 | 21 € | G | 10/11/2023 | 1 | 21 € | G | 10/11/2023 | 1 | 21 € | ||||||
12 | H | 30/09/2023 | 1 | 9 € | H | 30/09/2023 | 2 | 9 € | H | 30/09/2023 | 3 | 9 € | ||||||
13 | I | 03/03/2024 | 1 | 11 € | I | 03/03/2024 | 1 | 11 € | I | 03/03/2024 | 1 | 11 € | ||||||
14 | ||||||||||||||||||
Folha1 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B5:E13 | Expression | =SE($C5<$C$3;1) | text | NO |
The use of CF with a function I have never seen before (SE) and do not have available on MS 365 US version complicates things enough that I think you should start a new thread with your request.Accordingly keeping your VBA Formula here is the goals that would like to achieve with the Examples in Table 2 and Table 3 keeping the CF on.
To explain and show the results pretended and keep it KISS just made changes in cells I5, I9 and I12 and then N5, N9 and N12 and so on.
You are welcome - thanks for the reply.Dear @JoeMo 🖐
In order to help and clarify (SE) translated to US is equal to the IF function.
Yes, was thinking that would be possible to compatible both in other words through CF add one colour and VBA programmatically change the font color to a different one.
Will think if should start a new thread.
Again, thank you very much for your care and concern.
Will keep in touch.