excel gurus

[bonnie&clyde]

does anyone know how the formula to have all of A column change color if the G column does not contain the word Done??? TIA

[Silverback]

Conditional formatting is your friend

[bonnie&clyde]

been messing with it for over an hr and still cant get it to work

[sosoe]

Conditional formatting is your friend

This. I assumed he meant like Table Columns and when making a table column it gives you the option to pick a color. Idk, Im not a guru at Excel.

[Nash B.]

This is not a guru-level question.

[bonnie&clyde]

no i want "A" to be formated Red if "G" does not contain "Done"

[BERNIE MOSFET]

Does Column A contain anything, or just want it to be an indicator? If not set the A1 cell "=G1", copypasta for the column.

Select Column A.

Home Ribbon, center left is the "Conditional Formatting" > Highlight Cell Rules >
Text that Contains

First field type "done". formatting drop down to custom, change text and fill to red.

Repeat with field "0" and format with white text or whatever.

[bonnie&clyde]

yes column A contains numbers

[Gtracer]

does anyone know how the formula to have all of A column change color if the G column does not contain the word Done??? TIA

a 'IF' formula would accomplish this. You could also create a rule to color the cells.

You could end up with a complex formula depending on how many cells you are checking and what not. Might be easier to build a table then have your cell reference that table to match 'g' and populate 'A'.


But I am by no means an expert...

Edit: This is an IF formula I use mixed in with a Index and Match (for tables). Might get you going and you could just play around until it works for you, kind of a reverse engineering if you want?

=IF($B5<>"N/A",INDEX($K$4:$K$8,MATCH($B5,$J$4:$J$8,0)),INDEX($ K$4:$K$8,MATCH($A5,$I$4:$I$8,0)))

INDEX is your 'table' column and cell reference
Match is the main tab where your data is contained that you are trying to color

[BlackGT]

This should work.

Go to Conditional Formatting
Click "New Rule"
Choose "Use a formula to determine which cells to format"
In the box for "Format values where this is true:" enter the following
=C1="Done"
Where C1 = the cell that contains the value of "Done"
Click "Format"
Click "Fill" - Choose color
Click "Font" - Choose color
Click "Ok"
Click "Ok" again.

You will have to repeat for each cell...

[BERNIE MOSFET]

This should work.

Go to Conditional Formatting
Click "New Rule"
Choose "Use a formula to determine which cells to format"
In the box for "Format values where this is true:" enter the following
=C1="Done"
Where C1 = the cell that contains the value of "Done"
Click "Format"
Click "Fill" - Choose color
Click "Font" - Choose color
Click "Ok"
Click "Ok" again.

You will have to repeat for each cell...

This will make the cell highlight when it says "Done". Since he would want to have the A cell highlight in the absence of "Done" in the corresponding G cell, he'd want to do something like =IF($G$1="Done"),,TRUE)

And, yeah, it would have to be done on every cell in the column individually. bleh.

[jakesford]

Probably the fastest way to do it is through VBA, if I have time I'll try to post up the code later.

[bonnie&clyde]

a 'IF' formula would accomplish this. You could also create a rule to color the cells.

You could end up with a complex formula depending on how many cells you are checking and what not. Might be easier to build a table then have your cell reference that table to match 'g' and populate 'A'.


But I am by no means an expert...

Edit: This is an IF formula I use mixed in with a Index and Match (for tables). Might get you going and you could just play around until it works for you, kind of a reverse engineering if you want?

=IF($B5<>"N/A",INDEX($K$4:$K$8,MATCH($B5,$J$4:$J$8,0)),INDEX($ K$4:$K$8,MATCH($A5,$I$4:$I$8,0)))

INDEX is your 'table' column and cell reference
Match is the main tab where your data is contained that you are trying to color

ummmmmm what did u say??

[Chili]

I assume there is an Excel for Dummies book out there. If not try the help feature.

I should not be hard to figure this one out.

[Nash B.]

Go to a column you're not using and set the first cell equal to

IFERROR(SEARCH("done", G1),0)>0

or whatever row you're starting at (G2, G3, etc.), then fill down to the bottom of your sheet. Then add a conditional formatting rule to the A column to fill when the formula

COUNTIF($H$1:$H$9999,TRUE)>0

, or whatever your range containing the true/false from the search comparison is in.