Changing color in macro

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Hey I am trying to use the If function to change the color of the cell in a macro, for instance.

If(Sheet1!A2>0, (show Sheet1!A2 in color), Sheet1!A2)

TO explain better, if a previous cell is higher than 0, make the new cell green, if not leave it the same as before.

I want this to show results better.

Any help will be great thanks!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,701
What do you mean by previous cell? The references in your IF are all the same, indicating that there is no difference in reference between "previous" and "new" cell, which doesn't make sense.
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
It is a previous if statement, that I calculated. Basically the 3rd sheet of my workbook has
If ((and slope>0, r^2>.9)), r^2, "-"

Basically, if the slope is higher than 0, and the r^2 value is higher than .9, show the r^2 value in the cell.
Now I want to change the color of the cell, so that if the r^2 value is from .90 to .93 it would be shaded blue, from .93 to .95 it would be green... and so on.

Thanks for your help, i am really new to writing macros in general so this is all steps in the dark for me.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,701
Now I want to change the color of the cell, so that if the r^2 value is from .90 to .93 it would be shaded blue, from .93 to .95 it would be green... and so on.
You have mentioned 2 colours for the cell. How many more are covered by the phrase "... and so on."? You can colour cells with 3 different conditions using Conditional Formatting by the way.
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42

ADVERTISEMENT

I think i could have just figured it out with using conditional formatting, thanks so much!

As just a really basic question. Say I have a condition/formula and i want all the columns A-CS from rows 2-999 all to have the same way. What is the shorter way in a macros to do it rather than to type out

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B999"), Type:=xlFillDefault
Range("B2:B999").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C999"), Type:=xlFillDefault
Range("C2:C999").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D999"), Type:=xlFillDefault
Range("D2:D999").Select


And so on...

As you can tell... I am just a sad 20 year old learning excel macros for the first time.

Thanks again!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,701
Select the entire range and then add the Conditional Formatting.
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42

ADVERTISEMENT

How would you write it in the macro? other than each line out like i mentioned
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,701
First of all, what is the formula for the condition in the Conditional Formatting? Have you done a "macro record" to see what it looks like in VBA code? Have you done a "macro record" before selecting the whole range and then doing the Conditional Formatting?
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
I have done macro records (that's basically how i started with everything and learned from there)

my formula for the cells is

ActiveCell.FormulaR1C1 = _
"=IF((AND(Sheet2!RC[-1]>0, Sheet2!RC[48]>0.9)), Sheet2!RC[48],""-"")"

Then I go and apply both that formula and the conditional formatting to each the range of columns and rows.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,701
That's not what I asked. I asked for the formula in the Conditional Formatting. I assumed you'd chosen "Formula Is" in Conditional Formatting, and entered a formula. If not, then it should be even easier than I thought.

You can enter formulae, and apply conditional formatting to an entire range of rows and columns, and not just one column at a time.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top