{"id":413,"date":"2013-03-02T10:01:56","date_gmt":"2013-03-02T10:01:56","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=413"},"modified":"2020-04-23T10:54:59","modified_gmt":"2020-04-23T10:54:59","slug":"excel-vba-lesson-16-formatting-font-colors","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-16-formatting-font-colors\/","title":{"rendered":"Excel VBA Lesson 16 : Formatting Font and Background Colors"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-15-do-loop\/\">&lt;&lt;Lesson 15&gt;&gt;<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\">[Contents]<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-17-part-1-excel-vba-object\/\">&lt;&lt;Lesson 17&gt;&gt;<\/a><\/strong><\/h4>\n\n\n\n<p>In this Lesson, we will explore how to write Excel VBA code that formats the color of an MS Excel spreadsheet. Using &nbsp;Excel VBA&nbsp;code, we can change the font color as well as the background color of each cell effortlessly.<\/p>\n\n\n\n<p>Alright, let&#8217;s create a program that can format random font and background colors using a randomizing process. Colors can be assigned using a number of methods in Excel VBA, but it is easier to use the RGB function. The RGB function has three numbers corresponding to the red, green and blue components. The range of values of the three numbers is from 0 to 255. A mixture of the three primary colors will produce different colors.<br>\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><br>\nThe syntax to set the font color is<\/p>\n\n\n\n<p><strong>cells(i,j).Font.Color=RGB(x,y,x)<\/strong><\/p>\n\n\n\n<p>where x,y, z are&nbsp;any numbers between 1 and 255<\/p>\n\n\n\n<p>The syntax to set the cell&#8217;s background color is<\/p>\n\n\n\n<p><strong>cells(i,j).Interior.Color=RGB(x,y,x)<\/strong><\/p>\n\n\n\n<p>Where x,y, z can be any number between 1 and 255<\/p>\n\n\n\n<p>Some RGB Color Codes are shown in the following chart,<br>\n<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th>Color<\/th><th>RGB Code<\/th><\/tr><tr><td><\/td><td>(0,0,0)<\/td><\/tr><tr><td><\/td><td>(255,0,0)<\/td><\/tr><tr><td><\/td><td>(255,255,0)<\/td><\/tr><tr><td><\/td><td>(255,165,0)<\/td><\/tr><tr><td><\/td><td>(0,0,255)<\/td><\/tr><tr><td><\/td><td>(0,128,0)<\/td><\/tr><tr><td><\/td><td>(128,0,128)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 16.1<\/strong><\/h4>\n\n\n\n<p>In this example, clicking the command button changes the background colors from Cells(1,1) to Cells(7,1) according to the specified RGB color codes. It also format the font colors from Cells(1,2) to cells(7,2) using specified RGB color codes.<\/p>\n\n\n\n<p><strong>The code<\/strong><\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nDim i As Integer<\/p>\n\n\n\n<p>&#8216;To fill the cells with colors using RGB codes<br>\nCells(1, 1).Interior.Color = RGB(0, 0, 0)<br>\nCells(2, 1).Interior.Color = RGB(255, 0, 0)<br>\nCells(3, 1).Interior.Color = RGB(255, 255, 0)<br>\nCells(4, 1).Interior.Color = RGB(255, 165, 0)<br>\nCells(5, 1).Interior.Color = RGB(0, 0, 255)<br>\nCells(6, 1).Interior.Color = RGB(0, 128, 0)<br>\nCells(7, 1).Interior.Color = RGB(128, 0, 128)<br>\n&#8216;To format font color with RGB codes<br>\nFor i = 1 To 7<br>\nCells(i, 2).Value = &#8220;Font Color&#8221;<br>\nNext<br>\nCells(1, 2).Font.Color = RGB(0, 0, 0)<br>\nCells(2, 2).Font.Color = RGB(255, 0, 0)<br>\nCells(3, 2).Font.Color = RGB(255, 255, 0)<br>\nCells(4, 2).Font.Color = RGB(255, 165, 0)<br>\nCells(5, 2).Font.Color = RGB(0, 0, 255)<br>\nCells(6, 2).Font.Color = RGB(0, 128, 0)<br>\nCells(7, 2).Font.Color = RGB(128, 0, 128)<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"233\" height=\"323\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg\" alt=\"Excel VBA\" class=\"wp-image-447\"\/><\/a><\/figure><\/div>\n\n\n\n<p><strong>The Output<\/strong><br>\n<br>\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 16.2<\/strong><\/h4>\n\n\n\n<p>In this example, the font color in cells(1,1) and background color in cells(2,1) are changing for every click of the command button due to the randomized process.Rnd is a random number between 0 and 1, therefore&nbsp;255* Rnd will produce a number between 0 and 255 &nbsp;and&nbsp;Int(255*Rnd) will produce integers that take the values from 0 to 254<br>\nSo we need to add 1 to get random integers from 0 to 255.<br>\nFor example;Rnd=0.229<br>\n255*Rnd=58.395<br>\nInt(58.395)=58<\/p>\n\n\n\n<p><strong>The code<\/strong><\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()Randomize Timer<br>\nDim i, j, k As Integer<br>\ni = Int(255 * Rnd) + 1<br>\nj = Int(255 * Rnd) + 1<br>\nk = Int(255 * Rnd) + 1<br>\nCells(1, 1).Font.Color = RGB(i, j, k)<br>\nCells(2, 1).Interior.Color = RGB(j, k, i)<br>\nEnd Sub<\/p>\n\n\n\n<p><strong>The Output<\/strong><br>\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-15-do-loop\/\">&lt;&lt;Lesson 15&gt;&gt;<\/a>&nbsp;<a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\">[Contents]<\/a>&nbsp;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-17-part-1-excel-vba-object\/\">&lt;&lt;Lesson 17&gt;&gt;<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>&lt;&lt;Lesson 15&gt;&gt; [Contents] &lt;&lt;Lesson 17&gt;&gt; In this Lesson, we will explore how to write Excel VBA code that formats the color of an MS Excel spreadsheet. Using &nbsp;Excel VBA&nbsp;code, we can change the font color as well as the background color of each cell effortlessly. Alright, let&#8217;s create a program that can format random font &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-16-formatting-font-colors\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel VBA Lesson 16 : Formatting Font and Background Colors&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[22],"tags":[],"class_list":["post-413","page","type-page","status-publish","hentry","category-formatting"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel VBA Lesson 16 : Formatting Font and Background Colors - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"description\" content=\"This Excel VBA lesson illustrates how to format font and background color in Excel VBA\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/vba_lesson16.htm\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Lesson 16 : Formatting Font and Background Colors - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"This Excel VBA lesson illustrates how to format font and background color in Excel VBA\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/vba_lesson16.htm\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-23T10:54:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"233\" \/>\n\t<meta property=\"og:image:height\" content=\"323\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-16-formatting-font-colors\/\",\"url\":\"https:\/\/excelvbatutor.com\/vba_lesson16.htm\",\"name\":\"Excel VBA Lesson 16 : Formatting Font and Background Colors - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson16.htm#primaryimage\"},\"image\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson16.htm#primaryimage\"},\"thumbnailUrl\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg\",\"datePublished\":\"2013-03-02T10:01:56+00:00\",\"dateModified\":\"2020-04-23T10:54:59+00:00\",\"description\":\"This Excel VBA lesson illustrates how to format font and background color in Excel VBA\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson16.htm#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/vba_lesson16.htm\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson16.htm#primaryimage\",\"url\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg\",\"contentUrl\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg\",\"width\":233,\"height\":323,\"caption\":\"Excel VBA\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson16.htm#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel VBA Lesson 16 : Formatting Font and Background Colors\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel VBA Lesson 16 : Formatting Font and Background Colors - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"This Excel VBA lesson illustrates how to format font and background color in Excel VBA","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/excelvbatutor.com\/vba_lesson16.htm","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Lesson 16 : Formatting Font and Background Colors - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"This Excel VBA lesson illustrates how to format font and background color in Excel VBA","og_url":"https:\/\/excelvbatutor.com\/vba_lesson16.htm","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-23T10:54:59+00:00","og_image":[{"width":233,"height":323,"url":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg","type":"image\/jpeg"}],"twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-16-formatting-font-colors\/","url":"https:\/\/excelvbatutor.com\/vba_lesson16.htm","name":"Excel VBA Lesson 16 : Formatting Font and Background Colors - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson16.htm#primaryimage"},"image":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson16.htm#primaryimage"},"thumbnailUrl":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg","datePublished":"2013-03-02T10:01:56+00:00","dateModified":"2020-04-23T10:54:59+00:00","description":"This Excel VBA lesson illustrates how to format font and background color in Excel VBA","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson16.htm#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/vba_lesson16.htm"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/excelvbatutor.com\/vba_lesson16.htm#primaryimage","url":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg","contentUrl":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_figure12.1.jpg","width":233,"height":323,"caption":"Excel VBA"},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/vba_lesson16.htm#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel VBA Lesson 16 : Formatting Font and Background Colors"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/413","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=413"}],"version-history":[{"count":55,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/413\/revisions"}],"predecessor-version":[{"id":3460,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/413\/revisions\/3460"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=413"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}