Conditional Formatting (XP)

[milkyway]

Hello,
Excel provides only 3 levels of conditional formatting, I need more and I need to apply the formatting to a cell depending on value in different cell...How can I proceed?

What I need is to change the font and/or background color of the specific cell in order to track delivery due dates (i.e: on time, on track, delayed, possible delay, arrived...)

Thank you

[HansV]

You can use a custom number format to add some formatting options, but if you need a lot of different formatting, this is still too limited. You can use the Worksheet_Change event to change the formatting of a cell based on the value of another cell. See the attached crude example. Try to enter different numbers in cell B1 and see what happens in cell B3. You can right click the sheet tab ("Sheet1") and select View Code to inspect the code used for this.

[milkyway]

Hello Hans,

thank you for your reply, it works fine, but now how can I apply this to my situation: I have many items being tracked, each in different columns and the reference column for the formatting also changes, it is always 2 or 3 column away from the column I want to apply the formatting to.

example: Column AC is the target column where I want to apply the formatting and column AE is where I will be gathering my info to apply my formatting. This goes on for other columns as well, for instance: AF relates to AH, AI to AK, and so on...but the relative moves are not constant...

I don't know if it's any clear what I wrote...

[LegareColeman]

You are either going to have to define a rule that the code can use to determine what cells need to be formatted and what cell they depend on, or you are going to have to come up with a table of cell addresses that need to formatted and the corresponding cells that determine the format. You will also need to define the rules that determine what formatting you want and what the formatting is for each rule. If you can do one of those and get back to us we can help write the necessary code.

[milkyway]

Ok, I have the column that need to be formatted and the column where I have to get the data in order to apply the formatting.
Apply-to Condition
AC AE
AF AH
AI AK
AL AN
AO AQ
AR AT
AU AW
AX AZ
BA BC
BO BQ
BT BV
CD CF
CJ CL
CM CO
CP CR
CU CW
CX CZ
DA DM
DO DR
DT DW
DY EB
ED EF

Condition value Apply-to Formatting
0 White Font on Black Filling
1 White Font on Red Filling

the reason why I need this even though I have only 2 conditions, is becasue I already used the C.F. for other conditions in more than one column and I need to have them all standard so if I want to change the formatting applied accroding to one condition I don't have to go do it manually for the other columns where I already have some C.F. used.

Thank you guys

[HansV]

Wow! Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range, intOffset As Integer
Set rng = Intersect(Target, Union(Range("AE:AE"), Range("AH:AH"), _
Range("AK:AK"), Range("AN:AN"), Range("AQ:AQ"), Range("AT:AT"), _
Range("AW:AW"), Range("AZ:AZ"), Range("BC:BC"), Range("BQ:BQ"), _
Range("BV:BV"), Range("CF:CF"), Range("CL:CL"), Range("CO:CO"), _
Range("CR:CR"), Range("CW:CW"), Range("CZ:CZ"), Range("DMM"), _
Range("DRR"), Range("DWW"), Range("EB:EB"), Range("EF:EF")))
If Not rng Is Nothing Then
For Each cel In rng
Select Case cel.Column
Case 117
intOffset = -12
Case 122, 127, 132
intOffset = -3
Case Else
intOffset = -2
End Select
Select Case cel.Value
Case ""
cel.Offset(0, intOffset).Font.ColorIndex = xlColorIndexAutomatic
cel.Offset(0, intOffset).Interior.ColorIndex = xlColorIndexNone
Case 0
cel.Offset(0, intOffset).Font.Color = vbWhite
cel.Offset(0, intOffset).Interior.Color = vbBlack
Case 1
cel.Offset(0, intOffset).Font.Color = vbWhite
cel.Offset(0, intOffset).Interior.Color = vbRed
Case Else
cel.Offset(0, intOffset).Font.ColorIndex = xlColorIndexAutomatic
cel.Offset(0, intOffset).Interior.ColorIndex = xlColorIndexNone
End Select
Next cel
End If
Set cel = Nothing
Set rng = Nothing
End Sub

[milkyway]

Hi Hans,

I entered what you wrote but it doesn't work...it keeps giving me an error saying "type mismatch" and it highlights the Case "" line...it says <application-defined or object-defined error>

I am trying to understand the code to figure out why it isn't working but I am not familiar enough to figure it out...

[HansV]

My code does not take error values into account. I have attached the workbook with slightly expanded code. If you still get errors, please try to find out when they occur.

[milkyway]

Hi Hans,

I made some modifications to my sheet and all the offset is now -2, how do I alter the code to take this into consideration?

[HansV]

You can replace this part

Select Case cel.Column
Case 117
intOffset = -12
Case 122, 127, 132
intOffset = -3
Case Else
intOffset = -2
End Select

by

intOffset = -2

or you can even replace intOffset by -2 wherever it's used.

(Conditional formatting would be a lot easier here)

[dhfifield]

Hans,

I have a similar need and wonder if you could help me as well?

I have a table consisting of 7 columns by 10 rows (D12:J21). Depending on what happens elsewhere in th spreadsheet, an upper case X will be in one of the cells in the first row (D12:J12). I want the column with the X in it to retain "normal" foreground and background colors while the other cells in the other columns appear "greyed out" (or diminished in some way).

I looked at your code a couple of posts up, but I am too new at VB to fully understand how to make this work. Can you help?

Douglas

[HansV]

You don't need VBA code for this. Conditional formatting is sufficient:

Select D12:J21 (D12 should be the active cell within the selection.)
Select Format | Conditional Formatting...
Under Condition1, select Formula Is.
In the box next to it, enter the following formula:

=(D$12="X")

Click the Format... button.
In the Color dropdown (in the Font tab), select one of the greys.
Click OK twice.

Note: the formula is for cell D12, but Excel will propagate it to the rest of the selection. The column will vary from D to J, but the $ in front of the row number 12 means that the formula will always refer to row 12, regardless of the row the formatted cell is in.

[dhfifield]

Hans,

Perfect. I did not know that Excel would propogate the conditional formatting down the column to the other cells. This worked just like I wanted. The only modification I had to make to your formula was to change the equal sign to a "does not equal" sign because I wanted the "greyed out" look to apply to those columns without the X.

Thanks again. You guys rock.

Douglas

[HansV]

Oops, sorry, that should have been NOT equal. Glad you solved it yourself.

[Maverick]

Hans,

I am trying to figure out how the code works in your "crude example" attached with <post#=401848>post 401848</post#>.
I am not so familiar with VBA and want to ask you if you could explain your code to me.

MTIA

Regards,