Highlight row depending on cell input 1

[Navvy]

Hi all...

Another query!

The headers of my spreadsheet looks as follows:
Exp | Mth | Dte | Tiv | Int | C/P | Bar | L | H | Cut | Amt | Dig

If "Bar" = OT, and cell D1 = the amount in the Tiv column then change the colour of that line to orange.

Thks

 

[acron]

Use conditional formatting, but change the Condition type to Formula Is and not the default Cell Value Is. Use the formula

    =AND($D3=$D$1,$G3="OT")

To use that the first row you wish the condition to apply to but change the 3 to the number of thge row you have selected, and then copy and paste special formats to th eremaining rows.

A.C.

 

[acron]

I should have mentioned that the above apllies if your titles run from column A to L. If they are different you need to change the column references in the formula to suit.

A.C.

 

[larryww]

I didn't see if Acron had a tighter or solution, but this is a solution - correct and well tested, if I interpreted your problem definition correctly. I presume that the Key value for column Tiv (column 4) is in row 1 (i.e., cell D1), and headings are in row 2.

I will say that if the built-in conditional formatting approach works, that certainly beats having to run a macro. Furthermore, this is pretty heavily hardwired as to row and column placement; defined ranges would be strongly, strongly recommended instead (e.g., something like {range("TivColumn&quot.column} instead of 4).

Sub setRowColorsConditionally()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If (c.Column = 4) And (c.Row > 2) Then 'only look at 4th column, rows 3+
Rows(c.Row).Select 'select the row
'if same as cell D1, and column 7 is "OT"
If (Cells(1, 4) = c.Value) And (c.Offset(0, 3).Value = "OT&quot Then
Selection.Interior.ColorIndex = 45 'orange row
Else
Selection.Interior.ColorIndex = xlNone 'uncolored row
End If
End If
Next c
End Sub

 

[larryww]

Actually a cleaner way would be to add a column somewhere off to the right with this in row 3...
=+IF(D3<>D1,&quot;&quot;,IF(G3<>&quot;OT&quot;,&quot;&quot;,&quot;OrangeRow&quot)
and copy that cell up and down. Then conditionally format
that cell based on if contents are &quot;OrangeRow&quot;

Then just let the macro loop, copying that column's color to the whole rows, row by row.

This way the 4 and 7 are not hardwired; you can change orange to something else w/ the Excel interface rather than code; and redefine the condition in the new cell described above, rather than code. All the code would do is propagate the conditionally formatted cell across each row.

 

[larryww]

And finally I look at Acron's, and it's solid. My code could be useful in other situations, but use his way for your problem.

I'm actually very impressed at how Acron cooly and calmly used relative addressing within conditional formatting. I'd have never thunk about trying something so bold Furthermore, it used AND - verry crrevver!

Acron: your solution gives an interesting alternative result if you actually do let it use Cell Value instead of Formula (and make it Not Equal To), just for your amusement, in case you hadn't noticed it.

 

[Navvy]

Acron, Larry - thanks for the reply.

I have not used conditional formatting before - and the above does not seem to work. I selected the cells concerned and put in the formula but nothing happens when the condition is True - can u help?

 

[Navvy]

Acron,

Great looks like I got it to work! Still need some help, however.

I want the line to highlight from cell A - L rather than just one cell.

More importantly, how do I get the highlight to stay, because the value in D1 is constantly changing.

Here is my formula I used:

=AND(G10=&quot;OT&quot;,E10=D1)

...given that I was working on row 10. I eventually want this for the whole sheet.

 

[acron]

Navvy,

If you select the cells you want it to apply to (Ax to Lx), before entering the condition, then it shoul dapply to all such cells.

To answer thr second part, could you let me know the actula columns that apply to the following, e.g. is Exp = Column A ?

Exp | Mth | Dte | Tiv | Int | C/P | Bar | L | H | Cut | Amt | Dig

A.C.

 

[Navvy]

Thanks Acron.

I still can't highlight all cells.

Re: the second question, I would like the highlight to remain eventhough D1 changes.

Column A = Exp, Column B = Mth, etc etc. Thanks

 

[larryww]

Yee haw - you may just need [my] macro code after all, if I follow you. You want permanency (once colored, never uncolored), right?

I don't know what rabbit can be pulled to do it otherwise, though I'll be impressed if someone can.

 

[dsi]

Check out thread68-239882
Dale has made a wonderful sample file that illustrates the use of conditional formatting. Sounds like it fits into what you are trying to do.

 

[acron]

Do you mean that the condition

< and cell D1 = the amount in the Tiv column >

is not a permanent criteria ?

A.C.

 

[Navvy]

Thanks for you efforts guys, I really want to get this done!

Larry, I tried you suggestion and it works fine. However, I have to run the macro. I want the row to be highlighted automatically.

Conditional formatting will work fine, however, the value in D1 is constantly changing without user interference. So once D1 is equal to the value in the Tiv column then I want the highlight to remain, even if it changes.

Any other suggestions?

 

[larryww]

Once again, acron's solution was superior, except for the permanent color change requirement. If you yet do now go with the macro approach:

To make the sub run automatically, you could put the code in Sub Worksheet_Change, though it might be executed more often than you want. You might want to optimize the code then, add preliminary &quot;Exit Sub&quot;s, etc.

Remove the Else statement for permanent color change.

Consider if 45 is your best color.

I would still name ranges for the hard-coded code references, as I mentioned earlier. This is a technique really worth getting practiced and comfortable with, because hard-wired references and offsets are really, really lame for programming, when you can avoid them very simply.