conditional formatting with formula in cell

[cricked]

hello,

I want to conditional format my table based on a formula in another sheet, as i want to be able to quickly change the formula without having to go into the conditional formatting.
f.e.
AND(K$1>=$I8;K$1<=$J8;$E8="brand1";WEEKDAY(K$1;2)<>7)
AND(K$1>=$I8;K$1<=$J8;$E8="brand2";WEEKDAY(K$1;2)<>7)
--> i am using the same formula but for different brands --> different Colours so it would be way easier to change the formula only once and automate the linking to the brands and the conditional formatting

does anyone know how to do that?

any help and tips would be very much appreciated!

thanks cricked

[AliGW]

Welcome to the forum.

The bad news is that it cannot be done with an indirect reference in the CF dialog. However, there may be a way to do it with VBA.

Nonetheless, you should attach a sample workbook so that we can better advise: instructions at the top of the page.

[FDibbins]

Another possible option may be to have a few different rules - 1 with each of your formulas, that would get triggered based input in your other sheet?

[Matierk]

Hi , i want to stop the conditional formatting where my values stop in range D ( the values change everyday )

find what im using

Sub threecf()
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("A2", Range("A2").End(xlDown))

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$a$1")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")

'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With

With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

With cond3
.Interior.Color = vbYellow
.Font.Color = vbRed
End With

End Sub

[FDibbins]

Matierk welcome to the forum

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

(also, please add code tags to you post, my signature explains how to do that)

[cricked]

sorry for the late reply - the email went straight into junk and i did not realise.

hmm this is what i thought that i would need to use VBA.. it is unfortunate there is not an "evaluate" function so to evaluate any kind of text formula within the conditional formatting.
i actually will not need the various brands in the future so i think i will be fine for now - thanks though!