EXCEL Conditional Format VBA - Colors & Fonts

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello everyone. First of all, thanks a lot for your help and input.

I have been looking all over the web to find the code for the following (i am a newbee)

http://img51.imageshack.us/img51/9670/captureeu.jpg

(Conditional Formatting VBA to change the color of rows within a range if values in one column matches conditions given)

Above, I uploaded a screen capture of a simpler version of the the excelsheet im working on.

In the sheet I have linked A5 = C3, and from A5 to A22, I subtract increments of 0.5 from the cell above (So in other words A6 = A5 - 0.5) so when I write the macro those links got broken. So I gave up. I am trying to write a macro that will execute itself everytime I change the value in any of the three cells C1, C2 or C3 ..OR change the incremented value on column A explained above.

So lets say x = Value of Cell in Column A of the Range("A5:D22") for each row in the Range.
I want the macro to do the following
If Range("C3") >= x >= Range("C2") Then Color the corresponding row.Interior in the Range Gray
If Range("C2") > x > Range("C1") Then Color the corresponding row.Interior in the Range Blue and Font BOLD
For anything Else, Color the corresponding row.Interior in the Range Green and Font BOLD

This is the simple version with only 3 conditions, but my original .xls is much larger so I cant use Excel's conditional formatting feature:(

I really appreciate your help, thank you very much.

5 replies

Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
Have a read here, it may help in what your are trying to do.

https://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm
Posts
1
Registration date
Monday December 14, 2009
Status
Member
Last seen
December 16, 2009

Thanks for the reply.

I have seen that and many of those before. what i cant figure out it is: Say I have a range from A5 to D22. How can I put a condition on one column to color the corresponding row within the given range.

For example, (let "i" be the row numbers from 5 to 22)
if Ai holds one of the 7 conditions, then color Ai to Di to either one of 3 colors
if A1 holds one of the 7 conditions, then color A1 to D1 to either one of 3 colors
See what I wrote below - use offset in conjunction with select case statements shown in the ozgrid article. If you need an example, let me know..I use this method.
thanks
i am waiting your reply
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
DId you ask a question?
Use offset in conjunction with the select case statements and you can color from A to D.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too