PDA

View Full Version : FormatConditions.add to change cell color when something is entered



gtbreps_2
02-08-2017, 11:57 AM
I'm working on a large database file. The database is from F14 to a dynamic last column and last row. In every other column, starting at F, there are cells at various rows in the column where the user needs to input the value. These locations change with each iteration of the database and will be in the same rows across all columns. I want to create a conditional format that looks for cells in columns F,H,J,etc. that contain a formula and then format the adjacent cell in column G,I,K,etc (respectively) to highlight until a value is entered.

I've tried the following code. Using a double For...Next statement and an If statement, looking for cells with a formula. I get a run time error '1004' on the long line containing the format conditions code.



Dim lRow, lCol As Long
Dim n, i As Integer

lCol = ActiveSheet.Cells(11, Columns.Count).End(xlToLeft).column
lRow = ActiveSheet.Cells(rows.Count, "B").End(xlUp).Row


For n = 7 To lCol Step 2
For i = 15 To lRow
If Range(Cells(i, n - 1), Cells(i, n - 1)).HasFormula = True Then
Range(Cells(i, n), Cells(i, n)) = Range(Cells(i, n), Cells(i, n)).FormatConditions.Add(xlBlanksCondition, , Cells(i, n).Interior.Color = RGB(255, 255, 0))
End If
Next i
Next n


Any Help?