IF, Then VBA w/Conditional Formatting Question

rrrepp

New Member
Joined
Nov 2, 2011
Messages
10
Hi I'm really having trouble with this VBA. I'm average at best at using Visual Basic, but i've got a general understanding. I'm trying to look at two columns that have the answer 'yes' or 'no' in them (Column N & Column O) and depending on it being a yes i'd like to highlight a cell offset. If yes in Column N, then highlight cell in E. If yes in Column O, then highlight cell in K. Here is the code i've been writing but i keep getting errors at every turn. Thanks for the help.

Sub ConditionalFormat()

Dim ColN As Range
Dim ColO As Range

Set ColN = ActiveSheet.Range("N54:N425")
Set ColO = ActiveSheet.Range("O54:O425")
If ColN = Yes Then
Cell.Offset(0, -9).Interior.ColorIndex = 27
ElseIf ColO = Yes Then
Cell.Offset(0, -4).Interior.ColorIndex = 27
Else
End If
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi and welcome to the board.
If I understand what your posted code is attempting to do, then perhaps something like this?
Code:
Sub ConditionalFormat()

Dim RowToCheck As Long

For RowToCheck = 54 To 425
  If Cells(RowToCheck, "N").Value = "Yes" Then Cells(RowToCheck, "N").Offset(0, -9).Interior.ColorIndex = 27
  If Cells(RowToCheck, "O").Value = "Yes" Then Cells(RowToCheck, "O").Offset(0, -4).Interior.ColorIndex = 27
Next RowToCheck

End Sub
Hope it helps.
 
Last edited:
Upvote 0
This was EXACTLY what i needed! Thanks a lot! Just a side question, is it possible to adjust this to apply to a toggle active x button to turn it on and off? Thanks again!
 
Upvote 0
is it possible to adjust this to apply to a toggle active x button to turn it on and off?
The answer is probably yes... but I don't know what you mean by 'turning it on & off'.
As it is now, it will execute one time for each click of the button.

How do you want it to work?
 
Upvote 0
I would like to set up an active x toggle button that when clicked (pushed in) it runs the formatting you just provided. Then when unclicked (button looks popped back out) it removes the formatting. Does that make any sense?
 
Upvote 0
ok, try this.
Slap an active x toggle button on your sheet and assign this code to the button:
Code:
Private Sub ToggleButton1_Change()
  If ToggleButton1 Then
    Dim RowToCheck As Long
    For RowToCheck = 54 To 425
      If Cells(RowToCheck, "N").Value = "Yes" Then Cells(RowToCheck, "N").Offset(0, -9).Interior.ColorIndex = 27
      If Cells(RowToCheck, "O").Value = "Yes" Then Cells(RowToCheck, "O").Offset(0, -4).Interior.ColorIndex = 27
    Next RowToCheck
    ToggleButton1.Caption = "Unformat"
  Else
    Cells.Interior.ColorIndex = xlNone
    ToggleButton1.Caption = "Format"
  End If
End Sub
Note, the code above removes all background formatting on the sheet, not just the ones that got turned yellow. If you want that to be limited to just the cells turned yellow by this routine, let us know and we can do that instead.

Hope it helps.
 
Upvote 0
Thanks again for helping me with this. I'm really close, but I just need it to clear from rows 54 and down (425). Thanks
 
Upvote 0
ok, easy enough.
You only need to replace one word. (in this line)
Cells.Interior.ColorIndex = xlNone

Here are a couple options.
To clear the format of the entire rows from 54 to 425:
Rows("54:425").Interior.ColorIndex = xlNone

To clear the format of the entire range of E54 to K425:
Range("E54:K425").Interior.ColorIndex = xlNone

Or to clear rows 54 to 425 in columns E & K only:
Range("E54:E425,K54:K425").Interior.ColorIndex = xlNone

Either one you choose, that's all you need to do.

Hope it helps.
 
Upvote 0
I should also mention that if you're going to have other cells formatted
that you don't want cleared, we can just reverse the actions we used to
color them and clear them instead. (ie.)
Code:
Private Sub ToggleButton1_Change()
Dim RowToCheck As Long
If ToggleButton1 Then
  For RowToCheck = 54 To 425
    If Cells(RowToCheck, "N").Value = "Yes" Then Cells(RowToCheck, "N").Offset(0, -9).Interior.ColorIndex = 27
    If Cells(RowToCheck, "O").Value = "Yes" Then Cells(RowToCheck, "O").Offset(0, -4).Interior.ColorIndex = 27
  Next RowToCheck
  With ToggleButton1
    .Caption = "Unformat"
    .Font.Bold = True
    .ForeColor = &HFF& '(Red)
  End With
Else
  For RowToCheck = 54 To 425
    If Cells(RowToCheck, "N").Value = "Yes" Then Cells(RowToCheck, "N").Offset(0, -9).Interior.ColorIndex = xlNone
    If Cells(RowToCheck, "O").Value = "Yes" Then Cells(RowToCheck, "O").Offset(0, -4).Interior.ColorIndex = xlNone
  Next RowToCheck
    With ToggleButton1
    .Caption = "Format"
    .Font.Bold = False
    .ForeColor = &H80000012 '(Black)
  End With
End If
End Sub

Should've just done that to begin with I suppose...:lookaway:

Hope it helps.
 
Upvote 0

Forum statistics

Threads
1,223,170
Messages
6,170,474
Members
452,330
Latest member
AFK_Matrix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top