Coding for conditional formatting based on relative cells:

**Norgas** · 08-13-2012, 03:39 PM

Hello everyone. I am relatively new to VBA programming and even newer to the forums here. (First post actually.)

I am creating an excel sheet that I am using to track my attempts at a practice exam bank for a class I am taking. How can I code in VBA to get a cell to change its format based on the cells value compared to another cell in the same row, but a different column?

Example, if the cell E5 equals AA5, it will shade the cell green, but if it does not equal it is shades it red. If the value in the cell entered = X, I want it to be shaded blue. (It would be great if it could change the color of the font to the same shade the box is colored therefore "hiding the text" that was input so I can't see my previous answer the next time I attempt the question.)
Then for cell E6, I want it to look at the value in AA6, and so on.
The cell with the "reference value" will always be column AA, but the cell being input to may be E6, F6, G6, etc. (But E6 through N6 will always have the same answer being AA6)

There are 26 worksheets in this workbook that the code would apply to, so is there a way to apply this to the entire workbook instead of each worksheet? That is how I coded the Userform was on the "This Workbook" tab, but didn't know if this could be applied the same way.

I am using a Userform that I created to enter the data into the selected cell. The ideal is to be able to double click a cell on exam attempt 1 (Column), have the userform pop-up, click the answer you want, click enter, and have the pop up go away, and appropriately shade the cell the value was entered into.
So far I have figured out all of it except the conditional formatting. With this, I only want it to apply the formatting once an input to that cell has been made so that unattempted questions remain blank/white.

Here is the code I have for my userform

This is placed in the "This Workbook" section

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        
        If Target(1, 1).Address <> "$A$1" Then
        Cancel = True
        frmanswer.Show
    End If
    
End Sub

while this one is under the User forms code section:

 '<< Form position with respect to application window >>
Private Sub Userform_Activate()
    With frmanswer
        .Top = Application.Top + 400 '< change 125 to what u want
        .Left = Application.Left + 25 '< change 25 to what u want
        .Caption = Cells(ActiveCell.Row, 4)
                
    End With
End Sub



Private Sub cmdA_Click()
    ActiveCell.Value = "A"

End Sub

Private Sub cmdB_Click()
    ActiveCell.Value = "B"
End Sub

Private Sub cmdC_Click()
    ActiveCell.Value = "C"
End Sub

Private Sub cmdD_Click()
    ActiveCell.Value = "D"
End Sub
Private Sub cmdDelete_Click()
    ActiveCell.Value = ""
End Sub

Private Sub cmdEnter_Click()
    Unload Me
End Sub

Private Sub cmdX_Click()
    ActiveCell.Value = "X"
    
End Sub

I appologize if this post extended to the point to be hard to read, but I have researched this to the ends of the internet and have had a hard time finding anything that works.

Thank you in advance.