Any EXCEL experts or semi-experts here?

Canon EF 24-70mm f/2.8L II

12/10/2009 12:34:44 PM · #1

Hello,

I need to create an if/then function that will change a cell's color.

Example:

If Cell C2 < H2 THEN turn Cell C2 RED.

I know how to use "conditional formatting" where if C2 is equal to 100 then make it RED. But can't figure out how to do what I mentioned above.

Thanks,

bergiekat

Canon EOS-5D Mark IV

12/10/2009 12:45:43 PM · #2

See if this helps!
changing cell color for if/then statements

bmatt17

Canon EOS-600D/T3i Rebel

Canon EF 24-105mm f/4.0L IS

12/10/2009 12:51:47 PM · #3

it's been a while so I don't recall syntax, but if you're just looking for the basic If>Then structure:

If Cell(C2).value < Cell(H2).value then
cell(C2)>backcolor = vbred
else
cell(C2).backcolor = vbwhite
end if

Like I said it's been a while. You can record macros to figure out how to write the code.

Dirt_Diver

Nikon D3s

12/10/2009 02:18:11 PM · #4

Just use conditional formating

12/10/2009 02:26:51 PM · #5

Conditional formatting can do what you want. I'll assume that you are using Office 2007:
- Select the cell you want to format
- On the "Home" tab, click "Conditional Formatting" and select "Manage Rules"
- Click "New rule"
- Select "Use a formula to determine which cells to format"
- Enter "=C2<2" (without the quotes) into the formula box
- Click the "Format" button and select the formatting options you want
- Click OK
You can set multiple rules, and you can change the formatting of one or more cells in response to the value in a completely different cell. Very flexible and powerful.

12/10/2009 02:56:14 PM · #6

thanks gang...

I think I used a combo of all advice and here is what worked:

Using the conditional format I changed the "condition" drop down to FORMULA IS -
then I put in =IF(K2and selected red from the cell format box.

This worked. If k2 is ,than u2 - the bg turns red ! I have a huge complex spreadsheet that needs "differences" to POP OUT. I can now apply this formula all over the sheet !

Thanks.

Message edited by author 2009-12-10 14:56:27.

Canon EF 400mm f/5.6L USM

12/10/2009 03:06:41 PM · #7

Yep!
Once you get used to defining conditional formats based on formulas, you might never use the "quick & easy" selections again! It's one of the little-recognized capabilities of Excel.

Steef

Canon EOS-40D

12/10/2009 03:28:33 PM · #8

you mean, you don't have to write a macro in VBA to do stuff like this???

Dirt_Diver

Nikon D3s

12/10/2009 04:11:07 PM · #9

Originally posted by kenskid:

Another thing to remember is if you want to copy and past formulas that are the same distances apart you can remove any '$' from the formula and it will allow you to do this. One thing I've noticed is 2007 adds a ton of $ signs to formulas.

12/10/2009 04:21:50 PM · #10

LOL...no...it turns out to be a VBA code but it writes it as you go (in the background).

Originally posted by Steef:

you mean, you don't have to write a macro in VBA to do stuff like this???

12/10/2009 04:29:27 PM · #11

Poop...another issue...

I need to be able to copy and paste the "conditional formating" w/o overwriting the number in the cell.

Example:

Cell C2 is 1.25. When the formula is applied it will turn red. I can copy that cell to other cells and the formula follows, however, it replaces all the number in the new cell with 1.25. I need it to past the formula w/o overwriting the number.

Oh...I'm in Excel 2003.

Thanks

Olympus OM-D E-M5 Mark III

12/10/2009 05:18:37 PM · #12

Ok figured out.

Put the conditional formula in the top cell. When you're sure it is working you then:

Select cell where conditional formula is then scroll down and select all cells below. Click on Format - conditional format again...you will see that your formula is still showing...press ok and your formula copies down w/o overwriting the numbers in the cells.

Originally posted by kenskid:

GinaRothfels

Olympus 8mm f/1.8 M.Zuiko Digital ED Fisheye PRO Lens

12/10/2009 06:14:18 PM · #13

I hate to confess I'm still using Excel 1997, and I didn't even know one could do this. I didn't expect to learn new things about office programs at a photography site, so thanks for the lesson.

As for copying the conditional formatting, I presume that newer versions of Excel still have the paintbrush (format painter) tool.

Tamron AF 28-300mm f/3.5-6.3 XR Di LD Aspherical IF for Canon

12/10/2009 10:57:41 PM · #14

I use the formula option If/Then command often, however, I couldn't figure out how to use it to turn a cell a color according to the "answer" of a formula using two other cells ! With a push from people on DPC I was able to figure it out.

There is such a diverse group of people here that someone always knows something....however, I don't know what the paintbrush is in excel ! LOL...

Originally posted by GinaRothfels:

SDW

Canon EOS-60D

12/10/2009 11:41:29 PM · #15

My wife just purchased Microsoft Office Ultimate 2007 the other day with here school funds trough Microsoft. I was surprised when she got it for $59.95 (Reg. $679.95). You have to be a qualified student but an ultimate deal.

So if your a student and want the ultimate office 2007 for $59.95 here is the link.
Offer good till Dec. 31st, 2009.

I was using Office 2000 and there is a huge difference.

Message edited by author 2009-12-10 23:42:07.

GinaRothfels

Olympus OM-D E-M5 Mark III

Olympus 8mm f/1.8 M.Zuiko Digital ED Fisheye PRO Lens

12/11/2009 08:04:53 AM · #16

Originally posted by kenskid:

....however, I don't know what the paintbrush is in excel ! LOL...

I don't like the sound of that because it's a tool a use quite a bit. I really am going to have to upgrade one of these days, if only because Microsoft doesn't have support for my ancient version of Office, which does crash from time to time.

AJSullivan

12/11/2009 08:58:02 AM · #17

Originally posted by GinaRothfels:

Originally posted by kenskid:
....however, I don't know what the paintbrush is in excel ! LOL...

I don't like the sound of that because it's a tool a use quite a bit. I really am going to have to upgrade one of these days, if only because Microsoft doesn't have support for my ancient version of Office, which does crash from time to time.

Its still there. The Format Painter. I use it every day.

I love excel. Some of the formulas I've come up with are awesome and get so much stuff done. I really need to learn VBA though.

I like this formula haha:

=IF($A24="N/A",0,IF(LEFT(RatioForecast!M24,1)="F",RIGHT(RatioForecast!M24,LEN(RatioForecast!M24)-1),IF(ISERROR(RatioForecast!M24*VLOOKUP(VLOOKUP($A24,Baseline!$F$7:$I$31,3,FALSE),$53:$62,MATCH(M$1,$1:$1,FALSE),FALSE)),0,RatioForecast!M24*VLOOKUP(VLOOKUP($A24,Baseline!$F$7:$I$31,3,FALSE),$53:$62,MATCH(M$1,$1:$1,FALSE),FALSE))))

Five_Seat

Canon EOS-6D

Canon EF 24-105mm f/4.0L IS

12/11/2009 11:25:35 AM · #18

AJ, if you are doing advanced formulas like the one you listed below, it would be not be too much of a jump to get into VB. I taught my VB over the course of a few summer internships. Start by recording macros, examining the code and then adapting it to your own needs.

Also there are tons of resources online and solutions to so many different problems that other people have had...

Originally posted by AJSullivan:

Originally posted by GinaRothfels:

Originally posted by kenskid:
....however, I don't know what the paintbrush is in excel ! LOL...

I don't like the sound of that because it's a tool a use quite a bit. I really am going to have to upgrade one of these days, if only because Microsoft doesn't have support for my ancient version of Office, which does crash from time to time.

Its still there. The Format Painter. I use it every day.

I love excel. Some of the formulas I've come up with are awesome and get so much stuff done. I really need to learn VBA though.

I like this formula haha:

=IF($A24="N/A",0,IF(LEFT(RatioForecast!M24,1)="F",RIGHT(RatioForecast!M24,LEN(RatioForecast!M24)-1),IF(ISERROR(RatioForecast!M24*VLOOKUP(VLOOKUP($A24,Baseline!$F$7:$I$31,3,FALSE),$53:$62,MATCH(M$1,$1:$1,FALSE),FALSE)),0,RatioForecast!M24*VLOOKUP(VLOOKUP($A24,Baseline!$F$7:$I$31,3,FALSE),$53:$62,MATCH(M$1,$1:$1,FALSE),FALSE))))

bmatt17

Canon EOS-600D/T3i Rebel

Canon EF 24-105mm f/4.0L IS

12/11/2009 12:30:44 PM · #19

Agree, if you use EXCEL a lot it's well worth the effort to learn VBA(Visual Basic for Applications).

There is SO much you can do. In my old job I had macros that changed the file names of an entire directory to a predetermined format with the current date. Had one that would open up any text file and count the number of lines and so much more. You can do ALMOST as much as VB6 can do and EXCEL macros are not limited to working with EXCEL spreadsheets.

Also make sure your macro security settings are set to ask you before running any macros. Macros are capable of doing a lot, deleting files/folders writing to the registry etc.

AJSullivan

12/11/2009 12:46:09 PM · #20

Yeah, I might snag a book and see what I can learn.

I really wish colleges offered advanced excel cources....like an "EXCEL FOR BUSINESS"

Steef

Canon EOS-40D

Canon EF 400mm f/5.6L USM

12/11/2009 01:41:15 PM · #21

Originally posted by AJSullivan:

Yeah, I might snag a book and see what I can learn.

I really wish colleges offered advanced excel cources....like an "EXCEL FOR BUSINESS"

it's really not a bad idea. i use VBA with excel and access everyday at work for some pretty crazy things. if you have a CS background it'll come really easy to you.

Nikon AF-S NIKKOR 24-85mm f/3.5-4.5G ED VR

12/11/2009 01:49:07 PM · #22

Originally posted by AJSullivan:

Yeah, I might snag a book and see what I can learn.

I really wish colleges offered advanced excel cources....like an "EXCEL FOR BUSINESS"

Yes, and yes!
I'd highly recommend a good book to anyone wanting to learn VBA. It's easy to get started without one if you have at least some programming background (I had one course in BASIC back in High School and one course in FORTRAN 77 in college). At first I just muddled my way through, learned by recording macros and picking the code apart and by reading online resources. Unfortunately this does not teach the object-oriented functionality of VBA, and you *really* should learn that part of it to make best use of VBA. For simple macros, it's not a big deal, but for anything more complex it saves tons of time.

A big second for the idea of "Advanced Office Applications for Business" as a required course for both business and technical majors. It blows my mind when I see recent college grads that have only rudimentary knowledge of Excel, Word, and PowerPoint. These are the basic "tools of the trade" for business communication, and high-level proficiency with them is a *huge* differentiator between those who are just adequately productive, and those who are productive well beyond expectations.

AJSullivan

12/11/2009 01:59:15 PM · #23

Heres one for you guys:

I have a sheet that uses if statements to pull data from another sheet. That if statement essentailly says that if x1 = todays date, then pull that column. It works plenty well.

The issue is that at the end of day, we have to go into that sheet, highlight the info, copy - paste special - values, in order to not lose that data at midnight.

Were trying to come up with a good way to have that data fall into the columns and remain there. The best we've come up with is just adding a macro that does the copy and paste for you, but honestly clicking the macro button is only one step less than just copying and pasting.

Any ideas?

George

Nikon D750

12/11/2009 02:10:40 PM · #24

Why not just use Matlab? No UI since it's really only for engineering programming, but pretty simple and intuitive compared to Java and others. And you can do some extreme data analysis with it (basically anything mathematic, applied to all your data).