Row Color Based On Text

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
341
Office Version
  1. 2013
Platform
  1. Windows
Is there a method to cause all rows to have a specific highlight color based upon the text entry in column "A"? The text entry in this column of course would be "Sunday" as indicated in the example.
I should mention that the dates in column "A" are automatic based on the basic formula in this example: =SUM(A11+1). This formula example in my 2019 sheet gives Saturday--05 Jan 2019 because row 11 is Friday--04 Jan 2019. Thus the actual text "Sunday" only appears within the outcome as a result of the formula.
I have a sheet that I record the daily currency exchange rates across several currency types as seen below in the example for Sunday--31 Dec 2017.
What I would like to learn is how to cause every row for a Sunday to become a highlight color without selecting each row. While I realize I would only need to do this about 50 plus times it would sure be good to know how to do this in any event.

Column "A"____________Column "B"_____Column "C"_____Column "D"_____Column "E"_____Column "F"_____Column "G"_____Column "H"_____Column "I"_____Column "J"_____Column "K"
Date_________________EUR to USD_____USD to EUR_____CZK to USD_____USD to CZK____RUB to USD_____USD to RUB_____RUB to CZK____CZK to RUB_____EUR to CZK_____CZK to EUR
Sunday--31 Dec 2017___1.2004600______0.8330100______0.0470000_____21.2754800_____0.0173600_____57.6118300_____0.3692900_____2.7079000_____25.5403600_____0.0391500
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
A few things.

First, you are not using the SUM function correctly here:
Code:
[COLOR=#333333]=SUM(A11+1)[/COLOR]
If you just want to add 1 to A11, simply use:
Code:
=A11+1
To use both SUM and + is redundant. You typically use SUM to SUM up a multi-cell range, like this:
Code:
=SUM(A1:A10)

Regarding your question, you should be able to do what you want use Conditional Formatting.
It looks to me that column A may actually be a date with a Custom Format. Is that correct? Can you confirm that for us by telling us what the format on that cell is?

It makes a difference if it is a Date with a Custom Format or Text.
If it was text, you would simply select all rows in your range, and apply the following Conditional Formatting formula (assuming your first row is row 2):
Code:
=$A2="Sunday"
and then choose your formatting fill color.

If column A is a date with a Custom Format, use this Conditional Formatting formula instead:
Code:
=WEEKDAY($A2)=1
and then choose your formatting fill color.
 
Upvote 0
A few things.

First, you are not using the SUM function correctly here:
Code:
[COLOR=#333333]=SUM(A11+1)[/COLOR]
If you just want to add 1 to A11, simply use:
Code:
=A11+1
To use both SUM and + is redundant. You typically use SUM to SUM up a multi-cell range, like this:
Code:
=SUM(A1:A10)

Regarding your question, you should be able to do what you want use Conditional Formatting.
It looks to me that column A may actually be a date with a Custom Format. Is that correct? Can you confirm that for us by telling us what the format on that cell is?

It makes a difference if it is a Date with a Custom Format or Text.
If it was text, you would simply select all rows in your range, and apply the following Conditional Formatting formula (assuming your first row is row 2):
Code:
=$A2="Sunday"
and then choose your formatting fill color.

If column A is a date with a Custom Format, use this Conditional Formatting formula instead:
Code:
=WEEKDAY($A2)=1
and then choose your formatting fill color.

Thank you so much for your help. Yes it is a custom date as follows: dddd--dd mmm yyyy
I have corrected all the incorrect usage of =sum.
Will see about the rest of your reply tomorrow as it is close to midnight here in the Czech Republic.
Will let you know how it goes.
 
Upvote 0
This does not work. I can repeat here my question. I want to, without the necessity of picking each Sunday row, be able to highlight the entire row, each entire row that contains Sunday. In the past, I have simply picked on the row number identifier, or indicator, and then select the highlight color. To do this I had to select 52 individual rows. My question involves a method that eliminates this redundant selection of each Sunday row, but instead selects each entire row (from column "A" to the infinite end of the row) in one fell swoop and highlights all Sunday rows at once with the desired highlight color. When Conditional Formatting is selected there are many choices, so to begin with if I am to use Conditional Formatting I must know where to start and within any given choice within Conditional Formatting to know where and how to proceed. If submission of this, or any thread, allowed attached images or a doc file containing images then this could become less confusing. But because images and/or docs are not allowed then submission of threads sometimes requires one to write a one million page submission to hopefully explain what a picture worth one thousand words could easily do!!!!!!!!!!!!!!!!!!!!!!! Go figure!!!!!!!!!!!!!!!
 
Upvote 0
But because images and/or docs are not allowed
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

This does not work.

Conditional Formatting does in fact work (I use it all the time myself, and have confirmed that it will work in this case).
Most of the time, if it does not work it is because it is not being applied properly.

There are two very important facts when applying a Conditional Formatting Formula to a multi-cell range:
- Whatever range you select, write the formula as it applies to the very first cell in your selected range. If your formula is written properly, Excel will automatically adjust it for all the other ranges.
- To that end, you must understand relative vs. absolute cell referencing when writing your formula. See: [/COLOR]https://support.office.com/...ferences-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

So, in your example, let's say that you are electing to apply this to rows 11-100, so you first select rows 11-100.
So that means the first cell in our selected range is A11.
Because we want every column to be highlighted based on what is in column A, we want to "lock down" the column A reference, so we must put a dollar sign in front of it in our formula, like this: $A
Because we want the rows to float (so row 12 will look at A12, row 13 will look at at 13), we do NOT want to lock down the row reference. So do NOT place a dollar sign in front of it.
So the Conditional Formatting formula we will use is:
Code:
[COLOR=#333333]=Weekday($A11)=1
[/COLOR]
We then just select the color we want for our Conditional Formatting, and it should be complete!

If it is not working for you, please tell us the following:
- What range you are selecting
- What Conditional Formatting formula you are typing in
 
Last edited:
Upvote 0
If submission of this, or any thread, allowed attached images or a doc file
containing images then this could become less confusing. But because images
and/or docs are not allowed then submission of threads sometimes requires
one to write a one million page submission to hopefully explain what a picture
worth one thousand words could easily do!...Go figure!...


I wish I could help you with the conditional formatting - That's what I can't
figure out either.
https://www.mrexcel.com/forum/excel...matting-if-contents-two-cells-arent-same.html
BUT! in order to load images onto Mr. Excel.com's message
board up-load your image to your favorite image hosting site. I use
http://tinypic.com/
First you must have an image and to get it from your Excel file, arrange your
file on screen that way you want and on your PC [Alt][Print Screen] puts the
image on the clipboard. Once you've dont that pull up your favorite graphic
software. I use good old "Microsoft photo Editor" Paste your image and save
to your favorite file for such stuff and then follow the instructions at
TinyPic.com or whatever. Copy the URL to your clipboard or even paste it
into the notepad


With that done return to the [Post New Thread] or [Reply to Thread] dialog
box and and click on the [Insert Image] Icon. and paste your image URL
where it says and click on [OK].


Too simple and you knew how to do that? Well you can't tell the skill level
from the printed word.

Best of luck on the conditional formatting.
 
Upvote 0
SUCCESS - Conditional formatting is good stuff. I suppose now I could close this thread as I have enabled the help supplied by Joe4, but how to close a thread is beyond me, so go for it Joe4.
 
Upvote 0
SUCCESS - Conditional formatting is good stuff. I suppose now I could close this thread as I have enabled the help supplied by Joe4, but how to close a thread is beyond me, so go for it Joe4.
There is nothing to do to "officially" close them out (many times other and sometimes better solutions get posted after the fact, so we do not want to stop or discourage those).
Just responding back saying it has been solved is good enough.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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
Back
Top