EXCEL formula to reference cell in same row

Question

I want to apply conditional formatting to a cell when another cell in the same row is TRUE. I know how to do this with individual conditional formatting but I want to complete the objective with one rule.

EG. if G1 is true then A1 should be greenand so on for every row.

Answer 1

If you already have a rule for one cell, you can apply that rule to other cells, too.

Method 1: Select the cell with the format, then click Home ribbon > Conditional Formatting > Manage Rules. In the dialog that opens, find the rule and adjust the range in the "Applies to" dialog. Make sure that the formula uses a reference that is relative for the row number, for example =$G1 in row 1, then apply to other rows.

Method 2: Select the cell with the format, click the format painter icon on the home ribbon, then select the cells to which you want to apply the format.

Method 3: Use a table for your data. When you define a conditional format for a column, all new rows will inherit the format (and formulas and data validation) for that column.

Answer 2

I did this with VB instead. I checked the checkbox for a tick and then changed the colour of the cell 2 to the left of the tickbox.

Sub CheckBox_Colour_Checker()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, -2)
    If xChk.Value = xlOn Then
     .Interior.Color = RGB(0, 255, 0)
 Else
     .Interior.Color = RGB(255, 255, 255)
End If
End With
End Sub