Conditional Formating and Formula Help

[Eaks77]

Hey Everyone,

I'm dealing with conditional formating. I've set it up so that specific cells will have their fill colors change depending on information on a particular cell. I have roughly 125 rows where I need to change the conditional formating formula.

For Exmaple
Row #1 =OR($F$4="MT/G7", $F$4="NCA", $F$4="AHS", $F$4="H2", $F$4="Shuttle")
Row #2 =OR($F$6="MT/G7", $F$6="NCA", $F$6="AHS", $F$6="H2", $F$6="Shuttle")

The process of changing each cell with conditional formating is very tedious... I can't copy or drag down the conditional formating, errors occur.

So questions:
- Is there a way to reduce the above forumla so that I only have to change on cell. For example, something along the lines of =Or($F$4="Mt/G7", "NCA", "AHS") <- this doesnt work, but I just want to have to change one number... so from $F$4 to $F$6.. etc. Rather than doing it for every ""
-Ideally I'd like to set up the correct conditional formating and then copy or drag it down with Excel figuring out which cells it needs to apply too and chance the formula by 2 every time. But I havent been able to do so.

Any help would be greatly appreciate, thanks!

[Eaks77]

excel.jpg




I'd imagine a visual will help.... See attachement So the conditional formatting is on W4 & Y4 and will alter fill color based on F4. For each formula, the column will always be the same, but the row must change by 2.

[Eaks77]

Bump bump bump

[Eaks77]

Bump Bumped Bump

No Suggestions?

[Tony Valko]

I can't interpret anything from your screencap.

If you can post a SMALL sample file that'll give us a better idea of what you're wanting to do.

SMALL = no more than 20 rows worth of data.

[FDibbins]

I agree with Biff. have you looked at the pic you uploaded?

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Eaks77]

Hey,

Thanks for the heads up. I've clicked the attachement and it appears fine :s It opens up on a new page. I'll see if i can do anything different :S

[Eaks77]

Hey,

Thanks for the heads up. I've clicked the attachement and it appears fine :s It opens up on a new page. I'll see if i can do anything different :S

[FDibbins]

upload the sample file please

[Eaks77]

alrighty, will do. I'm attaching it to this message. I've simplified the Sample which makes the above messages irrelevant.

When changing the data in colum F (F4, F6, F8), Columns K, L, M will change the fill color. You can see this when changing F4. The changes in Columns K,L,M are conditional formatting. For Example, K4 has this =OR($F$4="MT/G7", $F$4="NCA", $F$4="AHS", $F$4="H2", $F$4="Shuttle"). Every second row needs this conditional formating. So K4, K6 & K8, need this. But the problem is that I haven't found an easy way to duplicate this forumla. I have to copy K4, paste into K6, then slight change the formula in K6 so that it's relevant for that row. So the forumla would change too =OR($F$6="MT/G7", $F$6="NCA", $F$6="AHS", $F$6="H2", $F$6="Shuttle"). This can become extremely time consuming. I wondering is there an easier way to condence this forumla down or another way?

Thanks, sample.xlsx

[Tony Valko]

Try this...

Based on the sample file...

Select the *entire* range K4:M9 starting from cell K4.
Cell K4 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Conditional Formatting

Use this formula:

=AND(MOD(ROW(),2)=0,OR($F4="MT/G7",$F4="NCA",$F4="AHS",$F4="H2",$F4="Shuttle"))

[Eaks77]

Awesome! That did it :D thanks a lot

If your able to... can you explain how that formula worked?

[Tony Valko]

You want to format every other row and the rows to format are even numbered rows.

We're testing that the row number is an even number:

MOD(ROW(),2)=0

With even numbered rows MOD(ROW(),2) will return 0. The odd numbered rows will return 1.

So, we test that the row number is an even number AND if cell F4 contains one of the values of interest.

[Eaks77]

Hey Again,

I tried implementing the same formula above into another section of the workbook. I took it as far as i could. It's funcionting about 80%... but it's duplicating the results for every row. if you see in the attached sample... How do i make it so that it only applies to the single row.

Thanks

[Tony Valko]

Refresh my memory and tell me what range you're wanting to highlight.

[Eaks77]

Sorry, i dont think that attachment was 100%. I've attached a new one. I want Cells F5, F7, F9, etc.... highlighted. I've got it working, but it's highlighting all of the cells. So for example, if C4=CMA & F4 = MT/G7, then only F5 should be red. Not F5 & F7 & F9....etc.

[Tony Valko]

I think you need to make the cell references relative.

From this:

=AND(MOD(ROW(),2)=1,AND($C$4="CMA",$F$4="MT/G7"))

To this:

=AND(MOD(ROW(),2)=1,AND(C4="CMA",F4="MT/G7"))

[Eaks77]

There we go! I should have caught that... thanks! Learning tons!

[Tony Valko]

Good deal. Thanks for the feedback!

[Eaks77]

Not sure if you'd be able to provide some help on my other post.

http://www.excelforum.com/excel-prog...html?p=3419157

Thanks

[Tony Valko]

Sorry, not much of a programmer.

Good luck on that one!