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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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,402
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,402
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,402
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,402
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,634
Members
410,804
Latest member
bluepinky
Top