Excel VBA Conditional Formatting AND-Function

Question

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))

is not empty Cell <>"" AND
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?

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
– Mistella, Commented Apr 18, 2018 at 13:16

Chronocidal · Accepted Answer · 2018-04-18 13:19:15Z

1

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 & ")")

answered Apr 18, 2018 at 13:19

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HPM Over a year ago

Thanks for your support as well :) Your function seems to be the same as the one from PEH and with this function the code is executed. 1. However as stated above, the formatting condition is in all cells the same one. They are all reffering to cell C9. How can I make this flexible? 2. Even though, the conditional formatting forumla is inserted the interior and font color didn't change. Do you know what's going on here? :)

Chronocidal Over a year ago

The formula is locked to Column C, but (if the top cell is Row 9) the row should change as the code moves down. I do notice you don't have a line in your shown code to delete existing Conditional Formatting - have you had a look at the Conditional Formatting that has been placed in from "Home" > "Conditional Formatting" to makes sure there is not other formatting overrriding the new stuff?

Pᴇʜ · Accepted Answer · 2018-04-18 13:48:54Z

1

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 & ")")

edited Apr 18, 2018 at 13:48

answered Apr 18, 2018 at 13:16

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

3 Comments

HPM Over a year ago

I have just updated my post, to explain more briefly what I intend to do. :)

HPM Over a year ago

Thanks, with this function the code is executed. 1. However, the formatting condition is in all cells the same one. They are all reffering to cell C9. How can I make this flexible? 2. Even though, the conditional formatting forumla is inserted the interior and font color didn't change. Do you know what's going on here? :)

Pᴇʜ Over a year ago

@HPM This should work as expected. Make sure you delete all old conditions that might already exists for this range, before applying a new condition!

Collectives™ on Stack Overflow

Excel VBA Conditional Formatting AND-Function

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related