Thank you for looking at my question. Attached is a file that has some Conditional Formatting on Columns C and D that is tied to the values in Column F which creates groups with two different shades of yellow. The values in Column F are determined by a formula tied to cell J1 that creates either groups of 4 or groups of 3, depending on which radio button is selected. There are some other macros that for some reason changes the priority of the two formulas in Conditional Formatting. So am trying to control it with VBA. The macro "color_rows" is based on a similar macro that I found online, but it does not work. The formula with top priority should is determined if the value in Column F is not even OR the cell in Column C is empty, with a fill color of RGB(255,255,153). I am trying to use the MOD function in the macro to determine if the value in Column F is odd or even. The formula with secondary priority is determined if the value in Column F is even, with a fill color of RGB(255,255,0). In addition to the macro not working, I am not sure if I need to delete the existing conditional formatting. Thank you for your help!Question1.xlsm
VBA to Control Conditional Formatting
-
redrocker1200 -
June 30, 2022 at 9:14 PM -
Thread is marked as Resolved.
-
-
-
Try this.
First delete all the conditional formatting rules that color the rows.
Note this code is placed in the "Players" sheet object module and replaces your existing code there, you can delete the code you had in Module 1
Code
Display MorePrivate Sub OptionButton1_Click() If OptionButton1.Value = True Then Range("I1").Value = 4: ColorRows End Sub Private Sub OptionButton2_Click() If OptionButton2.Value = True Then Range("I1").Value = 3: ColorRows End Sub Private Sub ColorRows() Dim x, i& With Sheet2.[c2].CurrentRegion.Resize(, 4) x = .Value For i = 2 To UBound(x, 1) If x(i, 4) Mod 2 = 0 Or x(i, 1) = vbNullString Then .Cells(i, 1).Resize(, 2).Interior.Color = RGB(255, 255, 153) Else .Cells(i, 1).Resize(, 2).Interior.Color = RGB(255, 255, 0) End If Next End With End Sub
The players will be colored and grouped whenever one of the option buttons is selected.
-
Hi KjBox, Very cool and thank you. I have a lot to learn. I am wondering if there is a way to color the empty cells with the lighter color also. The "vbNullString" does not seem to do it. I appreciate it.
-
Change the code to
Code
Display MorePrivate Sub ColorRows() Dim x, i& With Sheet2.[c2].Resize(49, 4) x = .Value For i = 2 To UBound(x, 1) If x(i, 4) Mod 2 = 0 Or x(i, 1) = vbNullString Then .Cells(i, 1).Resize(, 2).Interior.Color = RGB(255, 255, 153) Else .Cells(i, 1).Resize(, 2).Interior.Color = RGB(255, 255, 0) End If Next End With End Sub
-
Beautiful! Thank you.
-
-
You're welcome
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!