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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Select the entire range and then add the Conditional Formatting.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top