Assistance with MS365 - Excel (its up to date) and Conditional Formatting

[ladyck3]

Assistance with MS365 - Excel (its up to date) and Conditional Formatting

I have no clue how to use this tool anymore, ever since the update. I sure hope this is the right way to go about this. A few things you should about my capabilities.
I have been retired over a decade and if you don't use it, you do certainly lose it. In this case, if you were to provide an answer using VBA, please don't. I do not now,
nor have I ever understood it and I am so flustered right now that trying to teach this old dog "you just gotta do this or that" will fall on deaf ears. Steps, please...

The information attached is just a small part of the chart I am working on and have not included the entire workbook as it is copyrighted material. I am attempting to make changes to this data for myself and no one else. This is a crochet pattern and its worked from lower right to left and work up the chart from the bottom to the top. This segment has simply a capital "L".

Being left handed, I have cell number both right to left and left to right and the rows on once side even number, and the other the odd number rows.
I work left to right and upward....

So this is what I am attempting to do and that is, as you can see on the left, ever other row is either light blue or lavender. HOWEVER, the grey cells (the pattern stitches) are in grey. I need to change each row color to correspond with either the blue or lavender on all blank/white cells but the grey cells should remain untouched. I will place strategic "X's" in the appropriate spots to designate the type of stitch to use. I just need the colors of the white cells, per row, to change.

I figure conditional formatting? But I have absolutely NO BLOODY IDEA how to even start to play with a formula. Can you help??? PLEASE. And again, I was retired with tek-tips changed and have not really been using thie resource like I did when working so I hope that I am in the right category or location and I hope that I posted the inquiry properly. HELP! Seriously... I'm too flustered and frustrated to go on with this project without the kind help from y'all. SKIP you still around? Again, I just don't get VBA so if you write me code to F-whatever and enter it some place it does me no good because I don't know how to adjust or edit it. Can this be done via Conditional Formatting? OR... do I just abandon this project. I've had the pattern for 4 years, really REALLY want to make it but as is, its not written for "Mosaic" crochet, its meant for "Interlock" crochet only. I know, out of your realm but thus is my issue.

Most sincerely,
Laurie K.

 

[Jim E]

Can you simplify? As an example, do you want the background fill changed based on the value of a cell?

if A1 = A, change the color to red
If A1 = B, change the color to green
If A1 = blank, change the color to gray.

It sounds like this can be done by conditional formatting, with no VBA needed. Let us know!

 

[ladyck3]

Hi Jim,

Thanks for getting back to me. The numbers you see have NOTHING to do with any value to be concerned over. These are line numbers on left & right by 2x's, left the even rows 50, 48, 46, on the right the odd numbers.... 49, 47, 45. They should not be consideration of any conditional formatting.

I honestly don't understand the A1 references you mention, sorry.

It really, in theory seems simple, Im just not a conditional Formatting guru.

I will add X marks manually in the grey areas where shown...as needed. However to help make sense of where they belong are dependent as to whether they are on either a light blue row (color 1) or on a lavender row (color 2). I NEED the Grey to remain grey. All of the white/empty squares should be light blue or lavender every other line. All even number rows, light blue, all odd number rows lavender, or vice versa.

The ENTIRE chart is 150 columns across (width) and 150 rows in length. The sample is just a snippet. Those columns in yellow are just there (not to be touch) but rather, I guess you could call them anchor markings to help keep track of where you are while stitching.

Does this. At all clear some of this up? Im doing this from phone...sorry for any typos.

Laurie

 

[Jim E]

Hi, If you are uploading a sample file, I'm not seeing it.

A1, A2, etc. are cell references. If you are manually typing in a specific value in a cell, a conditional format will change it to whatever color you specify for that value, but it not based on the row or column.

So if you manually type in the letter B in a cell, you can have that cell or any other change color.
If you want every other row to be a different color, you can use Excel's banding option. Here's how:

• Select the block of cells.
• Go to the Home tab.
• Click Format as Table in the Styles group.
• Choose a style with banded rows.
• Confirm the range and whether your table has headers. You can also click on/off the other options to see the results.
• Done! The banded colors apply automatically.

Try this, maybe I'll get one right if I understand what your looking to do!

Regards

 

[Andrzejek]

Since we cannot see your example, we are going to play a guessing game.

You said:
" I need to change each row color to correspond with either the blue or lavender on all blank/white cells but the grey cells should remain untouched. [...] I just need the colors of the white cells, per row, to change."

my guess would be: you can Filter -- Filter by Color... and have your sheet show which Rows are displayed based on the color. So if you select 'No Fill' that will Filter your Rows and show only "blank/white" rows,

Just a guess here....

 

[Jim E]

Hi,

The A1, A2 are cell references in Excel's naming convention.
Excel sequentially labels all (width) columns in a worksheet starting with A, B, C, ... X, Y, Z, AA, AB.
Similarly, Excel sequentially numbers (height) all rows starting with 1, 2, 3 ...

As an example, cell A2 has a value of X.

A B C D E
1
2 X
3
4
5

Does this help? Conditional Formatting can be programmed to turn cell A2, or any other cell, any color you want. No value (or spaces) in A2 would leave the target cell's color unchanged. Since you said you would manually put an X in a cell, this could work for you.
As an example, if cell A2 had a value of X, conditional formatting could change cell E2 background color to blue, while the value in cell E2 remained unchanged.

Also, you mentioned the size of the work area, 150 by 150. If you highlight this block of cells with your cursor (the area will turn a shade of gray), you can use the steps in the previous message to turn every other row a different color, either from the options given or your own choices.

Regards