I have been trying to do this formatting with no success. I am trying to format the odd numbers 1 to 9 and all the other even numbers red and the even numbers between 2 to 10 and all the other odd numbers light gray. Dont know if this is the correct category for it but I think it is close. I have an attachment.
@Shinas, not sure I understand, but it sounds like you just need to use conditional formatting and isodd() or iseven() functions?
If you want to "ask" vba what's there I'm a simpleton, and tend to use brute force, such as below...
HTH
GC
Please Login or Register to view this content.
You've got numbers all over the sheet but I'm not sure which ones you want to format. The only ones formatting in your example lie within the range H5:M23. The shading you show does not match your description, because the odd number above 9 are not shaded gray. (Numbers in N5:N23 are yellow, which is not mentioned in your description.)
This does not require VBA; it can be done simply with conditional formatting. I have attached a sample, formatting the numbers in H5:M23. There are two rules:
Gray:
Formula:
Please Login or Register to view this content.
Red:
Formula:Please Login or Register to view this content.
If I didn't get the range right, adjust the Applies To range, and change the reference in the formula to the cell at the upper-left corner of the range.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Ok.. what I would like is A1 TO G21 to be formatted like the middle
Sorry man I have no idea what you send me or how to apply it.
Ok .. when I do conditional formating on A!:G21 using ISEVEN(A1) this works grate but I dont know how to exempt 2, 4, 6, 8 and 10.
Any ideas
Are you familiar with conditional formatting?
=if(iseven(A1), "True", "False")Originally Posted by Shinas
=if(isodd(A1), "True", "False")
The above formulas entered into conditional formatting one at a time with selected background colors different for each will do odd/even. If needing another color/option nest another if() statement instead of the "True/False" statements or turn to VBA and doctor the above code that checks to make it look for your conditions and instead of comparing existence of the color, assign it. (F1, "Conditional Formatting" if you haven't used this feature before)
no. I know almost nothing about excel. I am just learning some things because of this project
I see what you saying and it looks like it will work but I just do not know how to nest formulas. I keep getting some error that I do not understand that ether. I just want one set of numbers to be red and another to be light gray.
@Shinas, there's nothing wrong with that. The formulas get entered into conditional formatting dialog box by selecting "formula" --- to get this far you have to learn by doing … and I'd suggest parsing YouTube for an appropriate keyword search on something like; excel conditional formatting. The other alternative is being very clear on describing what you want to see where including colors and someone will probably upload your workbook with the formulas entered. The downside of that is you would still need to learn in order to make any changes.
I'll (attempt to!) attach a file you can look at but not sure it meets your needs. I did the conditional formatting formulas in A1 twice, once for odd, once for even, then clicked the paint brush and copied formatting to the block.
G.C.
I wish I could talk to you voice wise. But what you have, that I can do.The problem is this. When I get all even numbers to be red there is 5 odd numbers I need to be red too. 1, 3, 5, 7, 9
I have been checking out google but they just dont have a complete solution.
Well @Shinas, I drew a blank on the idea of using formulas for conditional formatting. Instead I put some user-defined functions into a VBA module and did the conditional formatting formulas using those.
Coding is "ugly, down and dirty" as I didn't take the time to try and figure out how to do it eloquently (hopefully someone else will "correct" my butt-ugly coding).
New copy of workbook is attached, coding is below. It only works because your exception list was single-digit - there's no way shape or form leaving this coding in this form is acceptable practice, sorry to provide such a poor example!
HTH,
GC
Please Login or Register to view this content.
@Shinas,
Below allows numbers greater than 9...
GC
Please Login or Register to view this content.
Is anybody even looking at the file I attached with a complete working conditional formatting solution in post 3? This can be done with a simple formula and no VBA.
You just need to change the Applies To ranges to A1:G21, and in the formulas change H5 to A1.
@6StringJazzer, I missed it … probably didn't hit reload on browser and replied to e-mail copy asking about #'s 1-9.
I sure do like your solution better than mine!
It would seem that you are 100% correct. I was just too stupid to see it. I have some question. Why did you give me 2 formula? You only did the odd numbers, do I change ISODD to ISEVEN? I am very confuse.
One formula is for gray, one for red. They cover all the conditions you asked for.
Gray:
Formula:Please Login or Register to view this content.
If the number is odd and >9, or even and <=10, turn it gray
Red:
Formula:Please Login or Register to view this content.
If the number is odd and <=9, or even and >10, turn it red
Beautiful, absolutely beautiful. It works like a charm. I don't know how you guys do this. You must have a strong drink beside you at all times. Thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks