Cell Content Formatting based on Cell BG colour

[JinkyJulie]

Hello everyone,

I have 4 spreadsheets with about 500 cells each. I wish to format the data based on the cell background colour.

Examples...

If the cell bg colour is blue, I would like the data to be White and in bold...

If the cell is yellow, I would like the data to be displayed in Black and also bolded.

I thought Conditional formatting would be the best route, but I cannot get it to work... I used to colour each cell by hand... It takes far too long...

Any help would be very much apprecitated...

Thanks you, again...

Julie

 

[Von Pookie]

Conditional formatting applies formatting based off of the cell contents, not based off of the current formatting.

How are you deciding what the background color should be? If each background color is set according to the value of the cell, you could set a conditional format to set both the background color and font formatting at one go.

Other than that, I believe doing what you want would require a macro.

 

[JinkyJulie]

My spreadsheets are actually year long calendars for four shifts.... The shifts are on a four-week rotating schedule of day shifts (yellow bg) and night shifts (blue background).

In order to read them on screen or after printing... it is nice to have the dates highlighted as described.

If I could get a macro to create these tables automatically, it would be so nice..... but for now... I do them by hand....

I have a macro to automatically change the colours of opposite shifts.... I have tried adapting that, to no avail.

That is why I come here... conditional formatting does not seem to work and I cannot get a macro to work.

 

[daniels012]

How are you entering shifts? By times?
Michael

 

[erik.van.geit]

Hi,

replacing thousands of words by a little example would help
how can we help creating a macro to produce a table, without knowing how this table looks like?

kind regards,
Erik

 

[JinkyJulie]

Apologies All..

I was trying to get something to show you... I had some troubles with the HTML Maker... all fixed now... Here's the example of my simple calendar...
Yellow day shift - Blue night shift - white day off... (Red text is pay day)

These are two copies of the four shifts... they are opposite... Does this help??

work_Sched_ALL.xls
	Q	R	S	T	U	V	W
2	March
3	S	M	T	W	T	F	S
4				1	2	3	4
5	5	6	7	8	9	10	11
6	12	13	14	15	16	17	18
7	19	20	21	22	23	24	25
8	26	27	28	29	30	31
9
Shift A

work_Sched_ALL.xls
	Q	R	S	T	U	V	W
2	March
3	S	M	T	W	T	F	S
4				1	2	3	4
5	5	6	7	8	9	10	11
6	12	13	14	15	16	17	18
7	19	20	21	22	23	24	25
8	26	27	28	29	30	31	d
Shift D

 

[erik.van.geit]

could this be a start ?

Sub test()
Dim c As Range

    For Each c In Selection
        With c
        
            Select Case .Interior.ColorIndex
            Case -4142  'no color
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = False
            Case 5      'blue
            .Font.ColorIndex = 2
            .Font.Bold = True
            Case 6      'yellow
            .Font.ColorIndex = xlAutomatic
            .Font.Bold = True
            End Select
            
            Select Case .Value
            Case 1, 15, 29
            .Font.ColorIndex = 3
            .Font.Bold = True
            End Select

        End With
    Next c

End Sub

it seems to me you would like to create the other shifts using code starting from shift A
but 3 colors to get 4 shifts: I do not understand
you can play with the code like this
if no color, make it blue+white+bold

            Case -4142  'no color
            .Interior.ColorIndex = 5
            .Font.ColorIndex = 2
            .Font.Bold = True

hope this helps,
Erik