Excel VBA Conditional Formatting AND-Function

0
votes

UPDATE:

I try to use conditional formatting for the following case:

If a cell in column C (starting with C9) Tabelle3.Range(Tabelle3.Cells(9, 3), Tabelle3.Cells(lastcell, 3))

  1. is not empty Cell <>"" AND
  2. fullfills a criteria which is stated in Tabelle4 Cell B2 Tabelle4.Range("B2")

its Interior.Color should be changed to Cellclr and its Font.Color to Fontclr

Start Old Post: I looked through various posts about conditional formatting but I couldn't find any, that is preciously solving my problem.

I want to apply conditional formatting to a Excel workbook which will be constantly extanded. Therefore, I wrote the following code: 

Sub ConForm()

Dim lastcell As Long
Dim Cellclr As Long
Dim Fontclr As Long

lastcell = Tabelle3.Range("A1048576").End(xlUp).Row
Cellclr = RGB(232, 245, 246)
Fontclr = RGB(26, 155, 167)

Set C = Tabelle3.Range(Tabelle3.Cells(9, 3), Tabelle3.Cells(lastcell, 3))

    With C.FormatConditions.Add( _
            Type:=xlExpression, _
            Formula1:="=AND($C9<>"""";"$C9.Value <= Tabelle4.Range(""B2"").Value)")
        .Interior.Color = Cellclr
        .Font.Color = Fontclr
    End With

End Sub

If I just use the following range and formula:

Range("C9")
Formula1:="=C9<>""""")

the code works out for Cell C9. However, as already mentioned, it should be this Formula 

=AND($C9<>"""";"$C9.Value <= Tabelle4.Range(""B2"").Value

be applied for the range

Tabelle3.Range(Tabelle3.Cells(9, 3), Tabelle3.Cells(lastcell, 3))

Does someone know where I made a mistake/mistakes and how to solve this issue?

2
vbaexcelconditional-formatting
If you look at the color-coding on this code, the End Sub is in red text. So is the End With. Red text is strings or numbers. Since I'm not sure what the formula should look like once it's in the conditional formatting, I'm not sure where there is a missing/extra "; but you should definitely take a closer look at that string.... – Mistella

2 Answers

1
votes

First, check the colours on your formatting to see what's a string and what's not - you have a mysterious extra " in the middle of your formula, which will prevent the code from compiling in the first place. You have also tried to put VBA code (Tabelle4.Range("B2").Value) into an Excel formula, which won't work.

If you want to fix the value of Tabelle4.Range("B2").Value when the macro is run, you can change

Formula1:="=AND($C9<>"""";"$C9.Value <= Tabelle4.Range(""B2"").Value)")

to 

Formula1:="=AND($C9<>"""";$C9<=" & Tabelle4.Range("B2").Value & ")")
1
votes

You need to concatenate the strings and values correctly.

Example:

MyVariable = "ccc"
result = "aaa" & "bbb" & MyVariable & "AnotherString"
'result is "aaabbbcccAnotherString"

I'm not sure what you tried but probably you meant something like

Formula1:="=AND($C9<>"""";" & Range("$C9").Value <= Tabelle4.Range("B2").Value & ")")

Or more likely something like

Formula1:="=AND($C9<>"""";$C9<=Tabelle4!B2)")

Update:

Formula1:="=AND($C9<>"""";$C9<=" & Tabelle4.Range("B2").Value & ")")