Cell Content Formatting based on Cell BG colour

JinkyJulie

New Member
Joined
Jun 28, 2005
Messages
26
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

New Member
Joined
Jun 28, 2005
Messages
26
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

New Member
Joined
Jun 28, 2005
Messages
26
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
QRSTUVW
2March
3SMTWTFS
41234
5567891011
612131415161718
719202122232425
8262728293031
9
Shift A
work_Sched_ALL.xls
QRSTUVW
2March
3SMTWTFS
41234
5567891011
612131415161718
719202122232425
8262728293031d
Shift D
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
could this be a start ?
Code:
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
Code:
            Case -4142  'no color
            .Interior.ColorIndex = 5
            .Font.ColorIndex = 2
            .Font.Bold = True
hope this helps,
Erik
 

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

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
Top